Re: ERROR: stack depth limit exceeded

On Thu, 7 Sept 2023 at 08:45, gzh wrote: > but is there a good way to find out which SQL statements have issues without > having to run all SQL statements, as it would be too expensive? Does your postgresql server log not contain the error with the statement at issue? Geoff

Re: TSQL To Postgres - Unpivot/Union All

On Thu, 20 Jul 2023 at 15:28, Anthony Apollis wrote: > I am attaching my TSQL and Postgres SQL: You're still missing some CREATEs, for example for temp_FieldFlowsFact. Even assuming your columns list is correct, I would still (and as a matter of habit) include the target column list in your

Re: TSQL To Postgres - Unpivot/Union All

On Thu, 20 Jul 2023 at 13:17, Anthony Apollis wrote: > The Postgres i used: INSERT INTO temp_FieldFlowsFact > SELECT "Account", "Calendar day", "Financial year", "Period", > > [snip] At the very least, include a column list in your INSERT statement. We have no way of checking where any of your

Re: libpq and multi-threading

On Wed, 3 May 2023 at 12:11, Michael J. Baars < mjbaars1977.pgsql.hack...@gmail.com> wrote: > The shared common address space is controlled by the clone(2) CLONE_VM > option. Indeed this results in an environment in which both the parent and > the child can read / write each other's memory, but

Re: Using CTID system column as a "temporary" primary key

On Tue, 28 Mar 2023 at 10:28, Sebastien Flaesch wrote: > Is the CTID a good choice? > I think if you're honest with yourself you already know the answer to this question. The only real solution is to update the legacy code to use the primary key, or (if that's not possible) change the table

Re: Re[2]: Getting error while upgrading postgres from version 12 to 13

On Tue, 21 Mar 2023 at 16:06, Geoff Winkless wrote: > On Tue, 21 Mar 2023 at 10:29, shashidhar Reddy < > shashidharreddy...@gmail.com> wrote: > >> Actually I was using the below command to check the compatibility, it >> worked without any issue with 12.6 but it is not

Re: Re[2]: Getting error while upgrading postgres from version 12 to 13

On Tue, 21 Mar 2023 at 10:29, shashidhar Reddy wrote: > Actually I was using the below command to check the compatibility, it > worked without any issue with 12.6 but it is not working with 1version 2.14 > time /usr/lib/postgresql/13/bin/pg_upgrade --old-bindir > /usr/lib/postgresql/12/bin

Re: tcp keepalives not sent during long query

On Thu, 15 Dec 2022 at 07:31, Willy-Bas Loos wrote: > > wbloos=# set tcp_keepalives_idle=120; > SET > wbloos=# show tcp_keepalives_idle; > tcp_keepalives_idle > - > 0 Are you connected in this psql session via tcp or unix domain socket? "In sessions connected via a

Re: Faster distinct query?

On Wed, 22 Sept 2021 at 21:05, Israel Brewster wrote: > I was wondering if there was any way to improve the performance of this > query: > > > SELECT station,array_agg(distinct(channel)) as channels FROM data GROUP BY > station; > > If you have tables of possible stations and channels (and if

Re: ON CONFLICT DO NOTHING ignored on bulk insert

On Tue, 25 May 2021 at 08:18, Andrus wrote: > Looking for a method to do bulk insert ignoring product foreign key > mismatches. > ON CONFLICT only works with unique constraints, it's not designed for what you're trying to use it for. Geoff

Re: updating PGDG 12 devel on centos7 requires llvm5.0

On Mon, 8 Mar 2021 at 16:15, I wrote: > Tried running yum update on my centos7 box. Get the following: > > Error: Package: postgresql12-devel-12.5-1PGDG.rhel7.x86_64 (postgresql_12) >Requires: llvm5.0-devel >= 5.0 > I tried adding the EPEL repo just in case that was the issue but it >

updating PGDG 12 devel on centos7 requires llvm5.0

Hi I'm clearly missing something obvious but it's passed me by what that might be, so a pointer would be appreciated... Tried running yum update on my centos7 box. Get the following: Error: Package: postgresql12-devel-12.5-1PGDG.rhel7.x86_64 (postgresql_12) Requires: llvm5.0-devel >=

Re: Need help how to reproduce MySQL binary to PosgreSQL

On Sun, 9 Aug 2020 at 12:49, Condor wrote: > Yea, I checked it, but because is 3 years old solution I expect these > functions to be implemented long ago and just have another names or to > have similar functions that do the same functionality. It's seems not, > Okay will use the solution from

Re: Order by lower(column-alias) doesn't work...

On Thu, 28 May 2020 at 13:14, Andreas Joseph Krogh wrote: > This works: > select p.firstname, p.lastname, p.firstname || p.lastname as fullname from > onp_crm_person p order by fullname; > > But this doesn't: > select p.firstname, p.lastname, p.firstname || p.lastname as fullname from >

Re: Lock Postgres account after X number of failed logins?

On Wed, 6 May 2020, 14:28 Stephen Frost, wrote: > Greetings, > > * Geoff Winkless (pgsqlad...@geoff.dj) wrote: > > On Wed, 6 May 2020 at 00:05, Tim Cross wrote: > > > Where Tom's solution fails is with smaller companies that cannot afford > >

Re: Lock Postgres account after X number of failed logins?

On Wed, 6 May 2020 at 00:05, Tim Cross wrote: > Where Tom's solution fails is with smaller companies that cannot afford > this level of infrastructure. Is there an objection to openldap? It's lightweight (so could reasonably be run on the same hardware without significant impact), BSD-ish and

Re: Get rid of brackets around variable

On Wed, 5 Feb 2020 at 10:48, Raul Kaubi wrote: > > DO $$ >> DECLARE >> cur cursor for >> select * from (values('logi_web'), ('logi_taustaprotsess')) as q (col1); >> BEGIN >> for i in cur LOOP >> RAISE NOTICE 'create table %_y2020m01 PARTITION OF % FOR VALUES FROM >> (''2019-12-01'') TO

Re: combination join against multiple tables

On Fri, 31 Jan 2020 at 15:25, David G. Johnston wrote: > On Friday, January 31, 2020, Geoff Winkless wrote: > >> Now the problem is that I would like to return all the rows from a, but >> with a single row where t2.b and t1.b match. >> > > So, the final,number of r

combination join against multiple tables

Hi I have a query involving multiple tables that I would like to return in a single query. That means returning multiple sets of the data from the first base table, but that's acceptable for the simplicity in grabbing all the data in one hit. An example set: CREATE TABLE t1 (a int, b int, c

Re: SQL Query Syntax help

On Wed, 22 Jan 2020 at 11:00, srikkanth wrote: > Can you please help me in writing the syntax for the below mentioned table. > Suggest looking at the crosstab function. https://www.postgresql.org/docs/current/tablefunc.html crosstab(text source_sql, text category_sql) Produces a

Re: REINDEX VERBOSE unknown option

On Thu, 21 Nov 2019 at 15:32, Peter J. Holzer wrote: > On 2019-11-21 09:43:26 +0000, Geoff Winkless wrote: > > It wasn't meant to be insulting, I meant "esoteric" in the strict > > sense: that you need to have specific knowledge to parse them. > > I didn't understa

Re: REINDEX VERBOSE unknown option

On Wed, 20 Nov 2019 at 22:48, Peter J. Holzer wrote: > > On 2019-11-19 11:37:04 +, Geoff Winkless wrote: > > Even if you do that you're still requiring the user to parse syntax > > according to esoteric rules. > > Oh, please. Those "esoteric rules" have bee

Re: REINDEX VERBOSE unknown option

On Mon, 18 Nov 2019 at 22:24, Peter J. Holzer wrote: > > On 2019-11-18 12:24:40 +, Geoff Winkless wrote: > > On Mon, 18 Nov 2019 at 11:46, Michael Paquier wrote: > > > On Mon, Nov 18, 2019 at 10:27:24AM +0100, Josef Šimánek wrote: > > > > This is clear once

Re: REINDEX VERBOSE unknown option

On Mon, 18 Nov 2019 at 11:46, Michael Paquier wrote: > On Mon, Nov 18, 2019 at 10:27:24AM +0100, Josef Šimánek wrote: > > This is clear once you understand what does it mean. I was aware of VERBOSE > > option of EXPLAIN and tried to use it without needed parentheses (the same > > way EXPLAIN can

Re: CASE(?) to write in a different column based on a string pattern

On Wed, 13 Nov 2019 at 16:24, Moreno Andreo wrote: > |foo |bar |baz | > 1234 > 5678 > 9012 > (hoping text formatting is ok... 1234 should go in column foo, 568 in > bar and 9012 in baz) > > Is it possible? Simplest way in plain SQL would be individual

Re: Is this a bug ?

On Wed, 23 Oct 2019 at 17:20, Geoff Winkless wrote: > For what it's worth, I can see a value to having > > SELECT 'this is quite a long string' >'which I've joined together ' >'across multiple lines'; > > although the advantage of it vs us

Re: Is this a bug ?

On Wed, 23 Oct 2019 at 17:09, Ron wrote: > As much as I hate to say it, MSFT was right to ignore this bug in the > standard. Standards are standards for a reason. It is almost never correct to deliberately ignore them. If you don't like them, then revise the standard. Historically Microsoft

Re: Is this a bug ?

On Wed, 23 Oct 2019 at 16:55, Ron wrote: > Then -- since the 'e' is separated from 'd' by a comma, the result should be > "4", not "3". > > No doubt: it's a bug, no matter what the Pg devs say. I'm confused why you consider that to be true. The count is checking for columns containing the

Re: Is this a bug ?

On Wed, 23 Oct 2019 at 16:42, Ravi Krishna wrote: > select count(*) from bugtest where fld1 in ('a','b','c' > 'd','e'); > > Note the missing comma after 'c'. > > PG takes it a syntactically right SQL and gives 3 as output. > > In SQLServer it errors

Re: SELECT returnig a constant

On Tue, 15 Oct 2019 at 14:35, Ray O'Donnell wrote: > > On 15/10/2019 14:28, stan wrote: > > I used to be able to return a constant value in a SELECT statement in > > ORACLE. I need to populate a table for testing, and I was going to do so > > like this: > > > > SELECT > > employee.id , >

Re: Is my lecturer wrong about PostgreSQL? I think he is!

On Thu, 10 Oct 2019 at 09:31, Wim Bertels wrote: > sometimes people are really a fan of certain product, > sometimes in combination with the thought that all other products are > bad; i don't know if this is the case, you could compare it with > soccer, a barcalona fan will never become a real

Re: with and trigger

On Wed, 29 May 2019 at 12:52, PegoraroF10 wrote: > This trigger will not work because Master record was not inserted yet. > That seems reasonable. Since the transaction is meant to be atomic any select within the query should return data from tables as they are at the start of the transaction,

Re: bigint out of range

On Thu, 16 May 2019 at 16:31, Daulat Ram wrote: > Hello team , > > We are getting ERROR: bigint out of range. Please help on this. > > > Bigint is -9223372036854775808 to 9223372036854775807. https://www.postgresql.org/docs/current/datatype-numeric.html

Re: multiple indexes on the same column

On Fri, 12 Apr 2019 at 11:54, Tiffany Thang wrote: > Can you provide a scenario where creating multiple indexes on the same > column would be beneficial? > When you have too much disk space? When your table writes are too fast?

Re: Forks of pgadmin3?

On Fri, 22 Mar 2019 at 16:25, wrote: > I know that I can do this in psql but it’s not handy with many columns. > I know this doesn't solve your root problem but for this issue you might find pspg helpful. https://github.com/okbob/pspg Geoff

Re: Casting Integer to Boolean in assignment

On Thu, 24 Jan 2019 at 16:00, Adrian Klaver wrote: > People are going to make mistakes that is a given. Eliminating a boolean > test is not going to change that. I still think that if you've got to the point where you're actually part-way through writing a clause you're unlikely to forget to

Re: Casting Integer to Boolean in assignment

On Thu, 24 Jan 2019 at 15:40, Adrian Klaver wrote: > delete from delete_test where > > and then forget the 'field =' part. Though my more common mistake along > that line is: > > delete from delete_test; > > At any rate, if it can be done it will be done. If you follow that logic, then having a

Re: Casting Integer to Boolean in assignment

On Thu, 24 Jan 2019 at 15:32, Geoff Winkless wrote: > DELETE FROM WHERE ; > > What would you be thinking that that ought to do? To be fair, I suppose that accidentally missing out a test but including an integer field DELETE FROM WHERE ; could do this. Not something I've

Re: Casting Integer to Boolean in assignment

On Thu, 24 Jan 2019 at 15:28, Adrian Klaver wrote: > On 1/24/19 7:21 AM, Geoff Winkless wrote: > > How could you even write a query like the one Thomas posted? It > > doesn't even look remotely sensible. > delete from delete_test where 1::boolean; *chuckle* You misunderstan

Re: Casting Integer to Boolean in assignment

On Thu, 24 Jan 2019 at 15:11, Tom Lane wrote: > People don't generally post to the lists after a type-mismatch error > catches a typo for them. So it's pretty hard to tell about "how > many" developers would find one behavior more useful than the other. > It is safe to say, though, that the same

Re: Casting Integer to Boolean in assignment

On Thu, 24 Jan 2019 at 14:28, David G. Johnston wrote: > To assist developers in avoiding the writing of buggy queries. Amazing how many of these developers find this a hindrance. If only they could see how helpful we're being to them. Geoff

Re: Casting Integer to Boolean in assignment

On Thu, 24 Jan 2019 at 11:04, Alexandre GRAIL wrote: > > And added to this weirdness is the fact that '1' or '0' (with quote) is OK. > The reason for that at least is that '1' and '0' are valid boolean values. https://www.postgresql.org/docs/9.5/datatype-boolean.html There's additional text

Re: simple division

On Wed, 5 Dec 2018 at 12:45, Gavin Flower wrote: > If you divide one integer by another, then it is logical to get an > integer as as the answer. Hmm. It might fit with what a computer scientist might expect (or rather, not be surprised about), but I don't think you can say that it's "logical".

Re: simple division

On Wed, 5 Dec 2018 at 09:13, Gavin Flower wrote: > SELECT ceil(10/4.0); > > Is what you want for that example. Except that implies that "number of people who can fit in a car" is a real number, not a whole. IMO it's fundamentally broken that SQL doesn't cast the result of a divide into a

Re: psql is hanging

On Fri, 30 Nov 2018 at 15:53, John Smith wrote: > We have a long script of sql that we run, several thousand lines of sql. If I > execute the script > from start to finish, somewhere in the middle of it, one sql command will > hang and take 2 to > 3 hours. During this time, "htop" shows 100%

Re: Code of Conduct plan

On Fri, 14 Sep 2018, 15:55 James Keener, wrote: > > > Yes. They can. The people who make the majority of the contributions to >> the software can decide what happens, because without them there is no >> software. If you want to spend 20 years of your life >> > > So everyone who moderates this

Re: Code of Conduct plan

On Fri, 14 Sep 2018 at 15:10, James Keener wrote: > I understand the concern, however, if you look at how attacks happen > >> it is frequently through other sites. Specifically under/poorly >> moderated sites. For specific examples, people who have issues with >> people on Quora will frequently

Re: Why order by column not using index with distinct keyword in select clause?

On Tue, 11 Sep 2018 at 13:56, Arup Rakshit wrote: > I have define a simple B Tree index on column *country* for users table. I > don’t understand why the order by column not using the index scan when > using *distinct* keyword in the select clause. Can anyone explain what is > happening here? >

Re: Re: Re: Allow Reg Expressions in Position function

On Mon, 20 Aug 2018 at 14:46, Nick Dro wrote: > My specific issue is alrady solved. > For the greater good I sent the email requesting to allow reg exp in the > position functions. > Not sure if you will implement it... Just wanted to let you know that the > limited capabilities of this

Re: Re: Allow Reg Expressions in Position function

On Mon, 20 Aug 2018 at 09:22, Nick Dro wrote: > > This incorrect. > SELECT position(substring('https://www.webexample.com/s/help?' FROM > '/(s|b|t)/') IN 'https://www.webexample.com/s/help?'); > > Gives 5. It's wrong. > On Mon, 20 Aug 2018 at 09:22, Nick Dro wrote: > > This incorrect. >

Re: Suggestion about logging only every n-th statement

On Wed, 20 Jun 2018 at 12:51, Janning Vygen wrote: > But for analyzing usage patterns it would be very nice to have this > combined with a sample_rate for logging. > >logging_sample_rate = n > > So each n-th statement will get logged regardless of execution time. > I think you would need to

Re: manipulating NUMERIC values in C extension

On Fri, 8 Jun 2018 at 13:44, John McKown wrote: > Have you considered the standard C library functions: "atoi()", "atof()", > "atol()", and "atoll()" ? Hi John My issue wasn't so much how to get a number out of the string, rather how to get that value back into a NUMERIC object to return back

Re: manipulating NUMERIC values in C extension

On Fri, 8 Jun 2018 at 13:47, Geoff Winkless wrote: > Answering my own question, looks like And just in case anyone googling the question comes across this, this example code works. #include "postgres.h" #include #include "fmgr.h" #include "utils/geo_decls.h&qu

Re: manipulating NUMERIC values in C extension

On Fri, 8 Jun 2018 at 13:27, Geoff Winkless wrote: > numeric_in looks like it might do what I want but to do that I would > have to build a FunctionCallInfo struct to do that, and I'm not 100% > clear how to do that either :( Answering my own question, looks like res = DatumG

manipulating NUMERIC values in C extension

Hi I'd like to be able to perform some manipulation on NUMERIC values in a C function; however the exposed functionality in numeric.h is pretty restrictive. I can see numeric_normalize will return a pointer to a string representation, which is workable, and if there were an equivalent

Re: Code of Conduct plan

On Tue, 5 Jun 2018 at 01:18, Tom Lane wrote: > I think you're forgetting the sequence of events. That was posted in > Feb 2016. In May 2016 we posted a draft CoC which was open for public > discussion, and was discussed extensively at a public meeting at PGCon > in that same month [1], and the

Re: Code of Conduct plan

On Sun, 3 Jun 2018 at 22:47, Tom Lane wrote: > In any case, we went over all these sorts of arguments at excruciating > length in 2016. It's quite clear to the core team that a majority of > the community wants a CoC. I don't think any useful purpose will be > served by re-litigating that

testing for DEFAULT insert value in ON CONFLICT DO UPDATE query

Hi Is there any way to tell if a conflicting row in an multi-line INSERT used the DEFAULT directive? I would like to be able to upsert a bunch of rows and only UPDATE the conflicting rows where the value set was not new - the way I do this for NULLable columns is to just write NULL in the INSERT

Re: [GENERAL] Multiple key error .

On 23 November 2017 at 11:37, Szymon Lipiński wrote: > table can have only one primary key. And most probably you already have one. To clarify, you can have multiple UNIQUE constraints on a table, but only one PRIMARY. Geoff

Re: migrations (was Re: To all who wish to unsubscribe)

On 22 November 2017 at 14:19, Vick Khera wrote: > > This sounds to me like you're going to run afoul of CAN-SPAM unsubscribe > rules. You should re-think this workflow. CAN-SPAM only applies to commercial email, "the primary purpose of which is the commercial advertisement or