I have a table with some rows and I try adding an identity column using the command:
ALTER TABLE sourceTable
ADD COLUMN ogc_fid int
GENERATED BY DEFAULT AS IDENTITY;
The command above is suggested as answer to this question:
But I get:
ERROR: column "ogc_fid" contains null values
SQL state: 23502
It looks like the existing rows of the table do not get the identity auto generated but result in this error (the command works fine if the table has no rows). Any ideas? Do I have to first generate values for the column I'm adding maybe using DEFAULT nextval('some_sequence')
? Or is there a better approach in PostgreSQL 10?