Adding 'serial' to existing column in Postgres

Question

I have a small table (~30 rows) in my Postgres 9.0 database with an integer ID field (the primary key) which currently contains unique sequential integers starting at 1, but which was not created using the 'serial' keyword.

How can I alter this table such that from now on inserts to this table will cause this field to behave as if it had been created with 'serial' as a type?

Answer 1

Look at the following commands (especially the commented block).

DROP TABLE foo; DROP TABLE bar;  CREATE TABLE foo (a int, b text); CREATE TABLE bar (a serial, b text);  INSERT INTO foo (a, b) SELECT i, 'foo ' || i::text FROM generate_series(1, 5) i; INSERT INTO bar (b) SELECT 'bar ' || i::text FROM generate_series(1, 5) i;  -- blocks of commands to turn foo into bar CREATE SEQUENCE foo_a_seq; ALTER TABLE foo ALTER COLUMN a SET DEFAULT nextval('foo_a_seq'); ALTER TABLE foo ALTER COLUMN a SET NOT NULL; ALTER SEQUENCE foo_a_seq OWNED BY foo.a;    -- 8.2 or later  SELECT MAX(a) FROM foo; SELECT setval('foo_a_seq', 5);  -- replace 5 by SELECT MAX result  INSERT INTO foo (b) VALUES('teste'); INSERT INTO bar (b) VALUES('teste');  SELECT * FROM foo; SELECT * FROM bar; 

Answer 2

You can also use START WITH to start a sequence from a particular point, although setval accomplishes the same thing, as in Euler's answer, eg,

SELECT MAX(a) + 1 FROM foo; CREATE SEQUENCE foo_a_seq START WITH 12345; -- replace 12345 with max above ALTER TABLE foo ALTER COLUMN a SET DEFAULT nextval('foo_a_seq');