Search code examples
postgresqlprimary-keyauto-increment

How to add an auto-incrementing primary key to an existing table in PostgreSQL?

I have a PostgreSQL table with existing data.

How do I add an auto-incrementing primary key without deleting and re-creating the table?

Solution

  • Versions of PostgreSQL v10+

    Suppose you have a table table_name, to which you want to add an auto-incrementing, primary-key id (surrogate) column. The recommended way is using the form GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY [ ( sequence_options ) ].

    e.g. (ref)

    ALTER TABLE table_name 
ADD COLUMN id INTEGER PRIMARY KEY GENERATED ALWAYS AS IDENTITY;

    or

    ALTER TABLE table_name 
ADD COLUMN id INTEGER PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY;

    Older versions of PostgreSQL

    This is not recommended but continues to be supported as of PostgreSQL v16.

    Note: The older SERIAL form was replaced with GENERATED ... AS IDENTITY in PostgreSQL v10 because SERIAL could cause problems, such as allowing an accidental override of the value and requiring more grants to allow inserts (PostgreSQL: serial vs identity).

    The following form was used:

    ALTER TABLE test1 ADD COLUMN id SERIAL PRIMARY KEY;

    Internally this SERIAL is not preserved, it is expanded at parse time into a SEQUENCE and a SET DEFAULT nextval({sequence_name}) (more detailed discussion), saving you from explicitly typing those as was required in older versions, as outlined here:

    Even Older Versions of PostgreSQL

    In old versions of PostgreSQL (prior to 8.x?) you had to do all the dirty work:

    ALTER TABLE test1 ADD COLUMN id INTEGER;
CREATE SEQUENCE test_id_seq OWNED BY test1.id;
ALTER TABLE test1 ALTER COLUMN id SET DEFAULT nextval('test_id_seq');
UPDATE test1 SET id = nextval('test_id_seq');