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

Question

I have a table with existing data  Is there a way to add a primary key without deleting and re-creating the table

User · Answer

I landed here because I was looking for something like that too  In my case  I was copying the data from a set of staging tables with many columns into one table while also assigning row ids to the target table  Here is a variant of the above approaches that I used   I added the serial column at the end of my target table  That way I don t have to have a placeholder for it in the Insert statement  Then a simple select   into the target table auto populated this column  Here are the two SQL statements that I used on PostgreSQL 9 6 4    ALTER TABLE target ADD COLUMN some column SERIAL  INSERT INTO target SELECT   from source

User · Answer

Updated - Thanks to the people who commented   Modern Versions of PostgreSQL  Suppose you have a table named test1  to which you want to add an auto-incrementing  primary-key id  surrogate  column  The following command should be sufficient in recent versions of PostgreSQL      ALTER TABLE test1 ADD COLUMN id SERIAL PRIMARY KEY    Older Versions of PostgreSQL  In old versions of PostgreSQL  prior to 8 x   you had to do all the dirty work  The following sequence of commands should do the trick     ALTER TABLE test1 ADD COLUMN id INTEGER    CREATE SEQUENCE test id seq OWNED BY test1 id    ALTER TABLE test ALTER COLUMN id SET DEFAULT nextval  test id seq      UPDATE test1 SET id   nextval  test id seq      Again  in recent versions of Postgres this is roughly equivalent to the single command above

User · Answer

To use an identity column in v10   ALTER TABLE test  ADD COLUMN id   int   bigint   smallint  GENERATED   BY DEFAULT   ALWAYS   AS IDENTITY PRIMARY KEY    For an explanation of identity columns  see https   blog 2ndquadrant com postgresql-10-identity-columns    For the difference between GENERATED BY DEFAULT and GENERATED ALWAYS  see https   www cybertec-postgresql com en sequences-gains-and-pitfalls    For altering the sequence  see https   popsql io learn-sql postgresql how-to-alter-sequence-in-postgresql

User · Answer

ALTER TABLE test1 ADD COLUMN id SERIAL PRIMARY KEY    This is all you need to    Add the id column Populate it with a sequence from 1 to count     Set it as primary key   not null    Credit is given to  resnyanskiy who gave this answer in a comment

[postgresql] How to add an auto-incrementing primary key to an existing table, in PostgreSQL?

Examples related to postgresql

Examples related to primary-key

Examples related to auto-increment