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

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

From

john snow

Date:

14 December 2017, 07:53:43

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!

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

From

James Keener

Date:

14 December 2017, 08:44:18

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@gmail.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.

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

From

Tom Lane

Date:

14 December 2017, 09:14:36

john snow <ofbizfanster@gmail.com> writes:
> 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.

This sounds like a bug that was fixed last week:

https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=ee5b595493e1609903d55709853f5276ba85c81d

You could apply that patch locally, or avoid IDENTITY until 10.2 is out.

            regards, tom lane