How to add a PostgreSQL 10 identity column to an existing table with rows?

Question

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:

• How to add a PostgreSQL 10 identity column to an existing table?

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?

Answer 1

I cannot reproduce this with current Postgres 10.3. (This error should never occur.) Simple test:

dbfiddle here

I suspect you are running an outdated point release. Postgres 10.2 fixed some bugs that fit your case exactly. The manual:

• Fix assorted failures to apply the correct default value when inserting into an identity column (Michael Paquier, Peter Eisentraut)

  In several contexts, notably COPY and ALTER TABLE ADD COLUMN, the expected default value was not applied and instead a null value was inserted.

If so, an upgrade should fix your problem.
Consider the project policy:

We always recommend that all users run the latest available minor release for whatever major version is in use.