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

postgresql primary-key auto-increment
xRobot picture xRobot · May 31, 2010 · Viewed 170.6k times · Source

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

Answer

leonbloy picture leonbloy · May 31, 2010

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

Related questions

Changing primary key int type to serial

Is there a way to change existing primary key type from int to serial without dropping the table? I already have a lot of data in the table and I don't want to delete it.

Read More
How to reset postgres' primary key sequence when it falls out of sync?

I ran into the problem that my primary key sequence is not in sync with my table rows. That is, when I insert a new row I get a duplicate key error because the sequence implied in the serial datatype …

Read More
Reset PostgreSQL primary key to 1

Is there a way to reset the primary key of a PostgreSQL table to start at 1 again on a populated table? Right now it's generating numbers from 1000000 and up. I want it all to reset and start to 1, keeping all …

Read More