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

alter-tableidentitypostgresqlpostgresql-10

I'm reading about the new Postgres 10 identity column, and saw how it is used to replace SERIAL columns when CREATE TABLE.

Is it also possible to add an identity column to an existing table?

For example, with the old SERIAL columns, I can do:

ALTER TABLE sourceTable ADD  COLUMN ogc_fid SERIAL;

With the identity columns, is there an equivalent of above? I tried:

ALTER TABLE sourceTable ADD  COLUMN ogc_fid GENERATED BY DEFAULT AS IDENTITY;

But got an error:

ERROR: syntax error at or near "BY" LINE 1: …R TABLE sourceTable
ADD COLUMN ogc_fid GENERATED BY DEFAULT

I'm thinking about replacing the SERIAL columns with IDENTITY columns in my scripts, and just wanted to check if this is feasible.

Best Answer

You need to declare the type of the column, too (INT or BIGINT or SMALLINT):

ALTER TABLE sourceTable 
    ADD COLUMN ogc_fid int                -- int or bigint or smallint
        GENERATED BY DEFAULT AS IDENTITY;

Also be sure to use the latest point release. As the IDENTITY has only been recently added, there were bugs affecting this particular command before Postgres 10.2. See: How to add a PostgreSQL 10 identity column to an existing table with rows?

Related Question