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?

Answer 1

(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.

Answer 2

ALTER TABLE test1 ADD COLUMN id SERIAL PRIMARY KEY; 

This is all you need to:

1. Add the id column
2. Populate it with a sequence from 1 to count(*).
3. Set it as primary key / not null.

Credit is given to @resnyanskiy who gave this answer in a comment.