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

From: john snow <ofbizfanster(at)gmail(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Subject: copy from csv and postgresql 10's new identity column type
Date: 2017-12-14 01:53:43
Message-ID: CAE67tvUa7EJwhwwYLUeFh7VDbKW_-5zOAi50mJkt13fGt34f6w@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

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!

Responses

Browse pgsql-novice by date

  From Date Subject
Next Message James Keener 2017-12-14 02:44:18 Re: copy from csv and postgresql 10's new identity column type
Previous Message Tom Lane 2017-12-13 19:52:42 Re: How to session_preload_libraries multiple libraries in postgresql.conf