Re: order by

On Thu, May 11, 2023 at 11:08 PM Ron wrote: > On 5/11/23 09:55, Marc Millas wrote: > > Thanks, > > I do know about index options. > > that table have NO (zero) indexes. > > > If the table has no indices, then why did you write "it looks like there > is something different within the *b-tree opera

Re: order by

On Thu, May 11, 2023 at 11:30 AM Marc Millas wrote: > On Thu, May 11, 2023 at 5:23 PM Adrian Klaver > wrote: > >> On 5/11/23 08:00, Marc Millas wrote: >> > >> > On Thu, May 11, 2023 at 4:43 PM Adrian Klaver < >> adrian.kla...@aklaver.com >> > > wrote: >> > >> >

Re: order by

On 5/11/23 09:29, Marc Millas wrote: Hi, I keep on investigating on the "death postgres" subject but open a new thread as I don't know if it's related to my pb. I have 2 different clusters, on 2 different machines, one is prod, the second test. Same data volumes. On prod if I do select col_a

Re: order by

On 5/11/23 09:55, Marc Millas wrote: Thanks, I do know about index options. that table have NO (zero) indexes. If the table has no indices, then why did you write "it looks like there is something different within the *b-tree operator* class of varchar"?  After all, you only care about b-tr

Re: order by

On 5/11/23 08:29, Marc Millas wrote: So how is the data getting from the third database to the prod and test clusters? For the machines hosting the third db, the prod and test clusters what are?: should I understand that you suggest that the way the data is inserted Do cha

Re: order by

On Thu, May 11, 2023 at 5:23 PM Adrian Klaver wrote: > On 5/11/23 08:00, Marc Millas wrote: > > > > On Thu, May 11, 2023 at 4:43 PM Adrian Klaver > > wrote: > > > > On 5/11/23 07:29, Marc Millas wrote: > > > Hi, > > > > > > I keep on investiga

Re: order by

On 5/11/23 08:00, Marc Millas wrote: On Thu, May 11, 2023 at 4:43 PM Adrian Klaver > wrote: On 5/11/23 07:29, Marc Millas wrote: > Hi, > > I keep on investigating on the "death postgres" subject > but open a new thread as I don't know i

Re: order by

On Thu, May 11, 2023 at 4:43 PM Adrian Klaver wrote: > On 5/11/23 07:29, Marc Millas wrote: > > Hi, > > > > I keep on investigating on the "death postgres" subject > > but open a new thread as I don't know if it's related to my pb. > > > > I have 2 different clusters, on 2 different machines, one

Re: order by

Thanks, I do know about index options. that table have NO (zero) indexes. Marc MILLAS Senior Architect +33607850334 www.mokadb.com On Thu, May 11, 2023 at 4:48 PM Adam Scott wrote: > Check the index creation has NULLS FIRST (or LAST) on both indexes that > are used. Use explain to see what

Re: order by

Check the index creation has NULLS FIRST (or LAST) on both indexes that are used. Use explain to see what indexes are used See docs for create index: https://www.postgresql.org/docs/current/sql-createindex.html On Thu, May 11, 2023, 7:30 AM Marc Millas wrote: > Hi, > > I keep on investigating o

Re: order by

On 5/11/23 07:29, Marc Millas wrote: Hi, I keep on investigating on the "death postgres" subject but open a new thread as I don't know if it's related to my pb. I have 2 different clusters, on 2 different machines, one is prod, the second test. Same data volumes. How can they be sharing the

Re: Order by in a sub query when aggregating the main query

I've changed the code to use order by in the aggregate and it seems there are no noticeable changes in the query performance. Thanks for the help. Best, Federico Caselli On Sun, 25 Sept 2022 at 00:30, Federico wrote: > > Understood, thanks for the explanation. > I'll work on updating the queries

Re: Order by in a sub query when aggregating the main query

Understood, thanks for the explanation. I'll work on updating the queries used by sqlalchemy to do array_agg(x order by x) instead of the order by in the subquery. > I think that right now that'd > incur additional sorting overhead, which is annoying. But work is > ongoing to recognize when the i

Re: Order by in a sub query when aggregating the main query

Federico writes: > A basic example of the type of query in question is the following (see > below for the actual query): > select w, array_agg(x) > from ( > select v, v / 10 as w > from pg_catalog.generate_series(25, 0, -1) as t(v) > order by v > ) as t(x) > group by w > This

Re: order by

Luca Ferrari writes: > The ORDER BY rejects non existent columns (right) but accepts the > table itself as an ordering expression. As others have noted, this is basically taking the table name as a whole-row variable, and then sorting per the rules for composite types. I write to point out that

Re: order by

On Thu, 2021-06-10 at 10:39 +0200, Luca Ferrari wrote: > I also realized that this "table to tuples" expansion works for GROUP BY too. > However, I'm not able to find this documented in GROUP BY, WHERE, > ORDER BY clauses sections into select documentation > https://www.postgresql.org/docs/12/sql-s

Re: order by

On Thu, Jun 10, 2021 at 8:40 PM Luca Ferrari wrote: > On Thu, Jun 10, 2021 at 10:15 AM Vijaykumar Jain > > when you provide a table in query in the order by clause, it is > > ordered by cols of that table in that order. > > Clever, thanks! > I also realized that this "table to tuples" expansion wo

Re: order by

On Thu, Jun 10, 2021 at 10:15 AM Vijaykumar Jain wrote: > you can run an explain analyze to check what is going on, > when you provide a table in query in the order by clause, it is > ordered by cols of that table in that order. Clever, thanks! I also realized that this "table to tuples" expansio

Re: order by

> Any hint? you can run an explain analyze to check what is going on, when you provide a table in query in the order by clause, it is ordered by cols of that table in that order. create table t(id int, value int); postgres=# explain (analyze,verbose) select * from t order by t;

Re: Order by not working

On 2021-02-17 08:45:05 +0100, Laurenz Albe wrote: > On Tue, 2021-02-16 at 16:11 -0600, Ron wrote: > > SQL is only intuitive to people who've done programming... :) > > SQL is quite counter-intuitive to people who have only done > procedural programming. Yes, different paradigm. SQL is more like a

Re: Order by not working

On Tue, 2021-02-16 at 16:11 -0600, Ron wrote: > SQL is only intuitive to people who've done programming... :) SQL is quite counter-intuitive to people who have only done procedural programming. Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com

Re: Order by not working

SQL is only intuitive to people who've done programming... :) Also, since your table names are only composed of lower case and underscores, the double quotes are not needed. On 2/16/21 1:41 PM, Dan Nessett wrote: Thanks to those who responded. I have solved my problem by noting the advice to

Re: Order by not working

Thanks to those who responded. I have solved my problem by noting the advice to use a select with order by. In particular, I need to export the data to a csv file anyway, so I use the following copy command: COPY (SELECT household_name, family_list, street_address, city, state, zip, phone_list,

Re: Order by not working

On Tuesday, February 16, 2021, Dan Nessett wrote: > Thanks Peter. The listing of the result is from pg-admin 4.30 using > view/edit data applied to the household_data table. In the past this has > always returned the table contents in the ORDR BY sort order. Do I need to > specify some preference

Re: Order by not working

Thanks, Dan > On Feb 16, 2021, at 12:11 PM, Ron wrote: > > What would you tell pgadmin? "Order this particular query -- out of all the > billion queries I might write -- in this particular manner?" > > No, that's not how things work. Just add an ORDER BY when you query the > table. > > On

Re: Order by not working

What would you tell pgadmin?  "Order *this* *particular* query -- out of all the billion queries I might write -- in *this particular* manner?" No, that's not how things work.  Just add an ORDER BY when you query the table. On 2/16/21 12:48 PM, Dan Nessett wrote: Thanks Peter. The listing of t

Re: Order by not working

Thanks Peter. The listing of the result is from pg-admin 4.30 using view/edit data applied to the household_data table. In the past this has always returned the table contents in the ORDR BY sort order. Do I need to specify some preference in pg_admin to guarantee this? Dan > On Feb 16, 2021,

Re: Order by not working

Not sure how you select the household > > The result is (only the first column is shown): > > household_name > > "Garcia" > "Armstrong" > "Armstrong" > "Bauer" > "Bauer" > "Berst" > "Berst" > "Minch ()" > "Berst" > “Besel” but unless you select from the resulting table using again an orde

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

På torsdag 28. mai 2020 kl. 15:26:42, skrev Tom Lane mailto:t...@sss.pgh.pa.us>>: Andreas Joseph Krogh writes: > Is there a way to define "sorting-rules" on custom-types so that I can have > ORDER BY and PG will pick my custom odering? You'd have to write your own type, which would be a lo

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

Andreas Joseph Krogh writes: > Is there a way to define "sorting-rules" on custom-types so that I can have > ORDER BY and PG will pick my custom odering? You'd have to write your own type, which would be a lotta work :-(. A possible partial answer is to define the composite type as f

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

På torsdag 28. mai 2020 kl. 14:50:54, skrev Geoff Winkless mailto:pgsqlad...@geoff.dj>>: 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 does

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 > onp_crm_

Re: order by not working in view ?

On Thursday, April 9, 2020, David Gauthier wrote: > psql (9.6.7, server 11.3) on linux > > In the copy/paste below, first 2 lines returned by a select on the view, > why didn't it sort on start_datetime correctly ? I would think that the > one started on 04-08 would come before the one on 04-09

Re: order by not working in view ?

David Gauthier writes: > In the copy/paste below, first 2 lines returned by a select on the view, > why didn't it sort on start_datetime correctly ? Putting an ORDER BY in a view is a bit dangerous (last I looked, it wasn't even legal in standard SQL). Yeah, the view will sort, but there is noth

Re: Order by and timestamp SOLVED

On 3/17/20 8:57 AM, Björn Lundin wrote: I am still not sure that this can be marked solved. I am trying to figure out how running a different version of psql once can affect another version of psql. That would seem to imply psql changed something on the server and AFAIK sorting/ordering is

Re: Order by and timestamp SOLVED

> > I am still not sure that this can be marked solved. I am trying to figure out > how running a different version of psql once can affect another version of > psql. That would seem to imply psql changed something on the server and AFAIK > sorting/ordering is done by the server not the clien

Re: Order by and timestamp SOLVED

On 3/17/20 12:28 AM, Björn Lundin wrote: So insert is bnl@ibm2:~/db$ psql Tidtagning är på. AUTOCOMMIT off psql (9.6.15, server 9.4.15) Skriv "help" för hjälp. Except you are using psql 9.6.15 against a 9.4.15 server. What happens if you use psql(9.4.15) to do sort query against 9.4.15 se

Re: Order by and timestamp SOLVED

17 mars 2020 kl. 15:05 skrev Tom Lane : > > =?utf-8?Q?Bj=C3=B6rn_Lundin?= writes: >>> What happens if you use psql(9.4.15) to do sort query against 9.4.15 server? > >> However AFTER I checked the bad sorting - sep/oct 2016 and early apr 2017 >> With psql 9.4 I connected with psql 9.6 again. >>

Re: Order by and timestamp SOLVED

=?utf-8?Q?Bj=C3=B6rn_Lundin?= writes: >> What happens if you use psql(9.4.15) to do sort query against 9.4.15 server? > However AFTER I checked the bad sorting - sep/oct 2016 and early apr 2017 > With psql 9.4 I connected with psql 9.6 again. > And now the sorting error is gone her too! Boy ..

Re: Order by and timestamp SOLVED

>> So insert is >> bnl@ibm2:~/db$ psql >> Tidtagning är på. >> AUTOCOMMIT off >> psql (9.6.15, server 9.4.15) >> Skriv "help" för hjälp. > > Except you are using psql 9.6.15 against a 9.4.15 server. > > What happens if you use psql(9.4.15) to do sort query against 9.4.15 server? So this is mo

Re: Order by and timestamp

On 3/16/20 2:50 PM, Björn Lundin wrote: But not for that data sep/oct 2016 Had a thought, what if on the ibm2 machine you do: UPDATE amarkets SET startts = '2016-09-30 13:00:00' WHERE marketid = 1.127278857; And then rerun: select * from amarkets order by startts; Yes really, otherw

Re: Order by and timestamp

On 3/16/20 2:28 PM, Björn Lundin wrote: 16 mars 2020 kl. 17:40 skrev Tom Lane >: =?utf-8?Q?Bj=C3=B6rn_Lundin?= > writes: Ooh - terrible sorry. The output from first post describing the database schema Was actually from my production mac

Re: Order by and timestamp

On 3/16/20 2:50 PM, Björn Lundin wrote: 16 mars 2020 kl. 20:26 skrev Adrian Klaver >: Per Tom's comment, what are the encodings? Just sent reply to his mail with the encodings Also I would point out that the problem occurs on the machine you are dumping/

Re: Order by and timestamp

> 16 mars 2020 kl. 20:26 skrev Adrian Klaver : > > Per Tom's comment, what are the encodings? Just sent reply to his mail with the encodings > Also I would point out that the problem occurs on the machine you are > dumping/restoring backwards 9.6 --> 9.4. Not sure if that is relevant or not,

Re: Order by and timestamp

> 16 mars 2020 kl. 17:40 skrev Tom Lane : > > =?utf-8?Q?Bj=C3=B6rn_Lundin?= writes: >> Ooh - terrible sorry. >> The output from first post describing the database schema >> Was actually from my production machine - a raspberry pi. >> The pi hold a db on an usb-disk, which is pg_dump()ed every n

Re: Order by and timestamp

On 3/16/20 11:56 AM, Björn Lundin wrote: Ooh - terrible sorry. The output from first post describing the database schema Was actually from my production machine - a raspberry pi. The pi hold a db on an usb-disk, which is pg_dump()ed every night and imported to ibm2 history db (the bad one) T

Re: Order by and timestamp

>> Ooh - terrible sorry. >> The output from first post describing the database schema >> Was actually from my production machine - a raspberry pi. >> The pi hold a db on an usb-disk, which is pg_dump()ed every night and >> imported to ibm2 history db (the bad one) >> The schema is identical to

Re: Order by and timestamp

On 3/16/20 9:15 AM, Björn Lundin wrote: 16 mars 2020 kl. 16:46 skrev Adrian Klaver >: On 3/16/20 3:03 AM, Björn Lundin wrote: Yeah, it's hard to think of any explanation other than "the query used a corrupt index on startts to produce the ordering".  But yo

Re: Order by and timestamp

=?utf-8?Q?Bj=C3=B6rn_Lundin?= writes: > Ooh - terrible sorry. > The output from first post describing the database schema > Was actually from my production machine - a raspberry pi. > The pi hold a db on an usb-disk, which is pg_dump()ed every night and > imported to ibm2 history db (the bad one)

Re: Order by and timestamp

> 16 mars 2020 kl. 16:46 skrev Adrian Klaver : > > On 3/16/20 3:03 AM, Björn Lundin wrote: Yeah, it's hard to think of any explanation other than "the query used a corrupt index on startts to produce the ordering". But your \d doesn't show any index on startts. So maybe there's

Re: Order by and timestamp

> 16 mars 2020 kl. 16:27 skrev Adrian Klaver : > > On 3/16/20 1:51 AM, Björn Lundin wrote: >>> 16 mars 2020 kl. 01:41 skrev Tom Lane >> >: >>> >>> Adrian Klaver >> > writes: On 3/15/20 2:33 PM, Björn Lundin wrote: > I then di

Re: Order by and timestamp

>>> >>> Is amarkets in more then one schema? >> Yes but the table is empty in other schema (’dry’) - and has less idexes >> It is also present in imports - but empty there as well > > Actually the below indicates it is in other databases. A schema would be a > namespace within a database, see h

Re: Order by and timestamp

On 3/16/20 3:03 AM, Björn Lundin wrote: Yeah, it's hard to think of any explanation other than "the query used a corrupt index on startts to produce the ordering".  But your \d doesn't show any index on startts.  So maybe there's more than one amarkets table? I realize that I have (basicall

Re: Order by and timestamp

On 3/16/20 1:51 AM, Björn Lundin wrote: 16 mars 2020 kl. 01:41 skrev Tom Lane >: Adrian Klaver > writes: On 3/15/20 2:33 PM, Björn Lundin wrote: I then did ’select * from AMARKETS order by STARTTS’ Is amarkets in more then one

Re: Order by and timestamp

On 3/16/20 1:49 AM, Björn Lundin wrote: 16 mars 2020 kl. 01:37 skrev Adrian Klaver >: On 3/15/20 2:33 PM, Björn Lundin wrote: Hi! I have an old database that behaves a bit strange. I keeps horse races in UK/IE. I have a program that continuously* adds record

Re: Order by and timestamp

>> Yeah, it's hard to think of any explanation other than "the query used a >> corrupt index on startts to produce the ordering". But your \d doesn't >> show any index on startts. So maybe there's more than one amarkets >> table? I realize that I have (basically) the same dataset on another m

Re: Order by and timestamp

> 16 mars 2020 kl. 01:41 skrev Tom Lane : > > Adrian Klaver writes: >> On 3/15/20 2:33 PM, Björn Lundin wrote: >>> I then did ’select * from AMARKETS order by STARTTS’ > >> Is amarkets in more then one schema? > > Yeah, it's hard to think of any explanation other than "the query used a > corr

Re: Order by and timestamp

> 16 mars 2020 kl. 01:37 skrev Adrian Klaver : > > On 3/15/20 2:33 PM, Björn Lundin wrote: >> Hi! >> I have an old database that behaves a bit strange. >> I keeps horse races in UK/IE. >> I have a program that continuously* adds record into a market table , >> described as below. >> *continuousl

Re: Order by and timestamp

> > (1) Suggest using "pastebin.com " for this kind of > data. It may not >look very pretty -- or readable at all -- on the viewer's end >depending on their settings (see below for example). Ok, sorry about that. https://pastebin.com/2XANMcF6

Re: Order by and timestamp

Adrian Klaver writes: > On 3/15/20 2:33 PM, Björn Lundin wrote: >> I then did ’select * from AMARKETS order by STARTTS’ > Is amarkets in more then one schema? Yeah, it's hard to think of any explanation other than "the query used a corrupt index on startts to produce the ordering". But your \d

Re: Order by and timestamp

On 3/15/20 2:33 PM, Björn Lundin wrote: Hi! I have an old database that behaves a bit strange. I keeps horse races in UK/IE. I have a program that continuously* adds record into a market table , described as below. *continuously means ’after each race’ which is ca 12:00 --> 23:00. I then did ’s

Re: Order by and timestamp

On 3/15/20 2:48 PM, Steven Lembark wrote: On Sun, 15 Mar 2020 22:33:35 +0100:wq Björn Lundin wrote: And to my surprise i get a result like this (note the order of column STARTTS) (1) Suggest using "pastebin.com" for this kind of data. It may not look very pretty -- or readable at all -

Re: Order by and timestamp

On Sun, 15 Mar 2020 22:33:35 +0100:wq Björn Lundin wrote: > And to my surprise i get a result like this (note the order of > column STARTTS) (1) Suggest using "pastebin.com" for this kind of data. It may not look very pretty -- or readable at all -- on the viewer's end depending on thei