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

alter-tableidentitypostgresqlpostgresql-10

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?

Best Answer

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.

Related Solutions

Sql-server – Dropping then adding a table to a subscription with Identity option Not for Replication causes errors

I have found a solution though adding:

@identityrangemanagementoption = N'manual'

when I re-add the article using sp_addarticle, means that the automatically created sps used to replicate the data don't try and update the identity column and thus are created succesfully on the subscriber rather than raising the error.

Sql-server – Inserting rows into other table whilst preserving IDENTITY

While it doesn't automatically prevent duplicates, you can disable the identity temporarily using the following, and then you would likely just want to set the identity seed to the highest value in the table:

 SET IDENTITY_INSERT dbo.tablename ON;

 INSERT ...

 SET IDENTITY_INSERT dbo.tablename OFF;

 DECLARE @sql NVARCHAR(MAX);

 SELECT @sql = N'DBCC CHECKIDENT(''dbo.tablename'', RESEED, '
   + RTRIM(MAX(id_column_name)) + ');' FROM dbo.tablename;

 EXEC sp_executesql @sql;

I'm not sure what your best course of action would be to correct duplicates. If you insert 1000 new rows after reseeding, it is likely that the source system will generate new identity values that will conflict. What you might consider doing is simply setting one of the identity values to generate numbers well above the range that the other table won't ever get to (say 1 billion). You can still use IDENTITY_INSERT to merge, but there will never be a conflict. This also makes it very easy to determine whether a row was generated locally or imported.

Best Answer

Related Solutions

Sql-server – Dropping then adding a table to a subscription with Identity option Not for Replication causes errors

Sql-server – Inserting rows into other table whilst preserving IDENTITY

Related Question