Re: copy from csv and postgresql 10's new identity column type

Not quite a workaround, but could you create a temporary table without the identity column and then insert into select?

I'd be interested in hearing a real resolution and or cause of the issue though.

Jim

On December 13, 2017 8:53:43 PM EST, john snow <ofbizfanster(at)gmail(dot)com> wrote:
>we're porting old dbf's to postgresql 10. currently, we have a dbf that
>did
>not have a defined primary key. in the postgresql 10 table, we want to
>define a technical primary key using postgresql 10's identity column.
>
>when we export the dbf data to a csv, the csv does not have data for
>the
>new primary key column, so the COPY FROM command fails and we get the
>error
>message saying null is not a valid value for the primary key column.
>
>our copy from command specifies only the non-primary key columns.
>
>we're thinking the error might be due to this:
>https://www.postgresql.org/docs/10/static/sql-copy.html
>For identity columns, the COPY FROM command will always write the
>column
>values provided in the input data, like the INSERT option OVERRIDING
>SYSTEM
>VALUE.
>
>does the above documentation mean that when using COPY FROM command to
>populate a new table with the new identity column type as primary key,
>auto-id generation is disabled and we have to provide the id values
>ourselves?
>
>any workaround?
>
>thanks for any help!

--
Sent from my Android device with K-9 Mail. Please excuse my brevity.