Re: BUG #16300: Text line order corruption with COPY command

On Thu, Mar 12, 2020 at 1:05 PM PG Bug reporting form <
noreply(at)postgresql(dot)org> wrote:

> The following bug has been logged on the website:
>
> Bug reference: 16300
> Logged by: Hans Buschmann
> Email address: buschmann(at)nidsa(dot)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.

David J.