Bug reference: 16300 Logged by: Hans Buschmann Email address: buschmann@nidsa.net PostgreSQL version: 12.2 Operating system: Windows Server 2019 64bit Description:
A reproducable line order corruption occurs when copying a quite large test file into Postgres.
IIUC you copied data into PostgreSQL and then immediately read it out using SELECT without an ORDER BY and expected to get the same physical row order as the inserted data. Nothing promises that things will work this way though often times they do.
If order matters to you you need to add an ORDER BY. Since there is nothing natural to order by here you have to decide on how you want to change your setup. I believe that adding a bigserial column to the table will result in the sequence generator applying numbers in strictly ascending order matching the input order and then you can sort on that column. This is probably the simplest solution. You can add row numbers to the source file just before importing it. Or you can import the entire file to a text field and then split_to_array and rely upon that ordering.