5

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.

  • 1
    The link you have provided shows how to upgrade. Just scroll down till Upgrading – McNets Feb 25 '18 at 15:51
9

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?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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