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

Formatting output (was: Order by and timestamp)

On 2020-03-15 16:48:35 -0500, 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 prett

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

Order by and timestamp

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 ’select * from AMARKETS order by STARTTS’ A