8

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?

1
  • 2
    Please add your current CREATE TABLE
    – McNets
    Mar 13, 2018 at 18:21

1 Answer 1

6

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.

1
  • 2
    You were right, after I upgraded to 10.3.1 the query worked fine and the identity column was added successfully with auto generated values. Thanks! Mar 14, 2018 at 5:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.