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:
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!