Adding 'serial' to an existing column in Postgres

Question

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

How can I change this table so that from now on, inserts into this table cause this field to behave as if it were created using 'serial' as a type?

Answer 1

Look at the following commands (especially the commented out 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 the sequence from a specific point, although setval does the same as Euler's answer, for example

 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'); 

Answer 3

Non-interactive solution

Just adding to two other answers, for those of us who should have this Sequence created by a non-interactive script , when fixing, for example, a real-time DB.

That is, when you do not want the SELECT value manually and enter it yourself in the subsequent CREATE statement.

In short, you can not do this:

 CREATE SEQUENCE foo_a_seq START WITH ( SELECT max(a) + 1 FROM foo); 

... because the START [WITH] clause in CREATE SEQUENCE expects a value , not a subquery.

Note. Typically, this applies to all non-CRUD SELECTs (i.e.: nothing but INSERT , SELECT , UPDATE , DELETE ) in pgSQL AFAIK.

However, setval() does! Thus, it is absolutely true:

 SELECT setval('foo_a_seq', max(a)) FROM foo; 

If there is no data, and you do not know (want) about it, use coalesce() to set the default value:

 SELECT setval('foo_a_seq', coalesce(max(a), 0)) FROM foo; 

In an unrelated note, you can also specify a column that owns Sequence directly from CREATE , you do not need to change it later:

 CREATE SEQUENCE foo_a_seq OWNED BY foo.a; 

Eventually:

 CREATE SEQUENCE foo_a_seq OWNED BY foo.a; SELECT setval('foo_a_seq', coalesce(max(a), 0)) FROM foo; ALTER TABLE foo ALTER COLUMN a SET DEFAULT nextval('foo_a_seq'); 

---

Using Function

Alternatively, if you plan to do this for multiple columns, you can choose to use the actual Function .

 CREATE OR REPLACE FUNCTION make_into_serial(table_name TEXT, column_name TEXT) RETURNS INTEGER AS $$ DECLARE start_with INTEGER; sequence_name TEXT; BEGIN sequence_name := table_name || '_' || column_name || '_seq'; EXECUTE 'SELECT coalesce(max(' || column_name || '), 0) + 1 FROM ' || table_name INTO start_with; EXECUTE 'CREATE SEQUENCE ' || sequence_name || ' START WITH ' || start_with || ' OWNED BY ' || table_name || '.' || column_name; EXECUTE 'ALTER TABLE ' || table_name || ' ALTER COLUMN ' || column_name || ' SET DEFAULT nextVal(''' || sequence_name || ''')'; RETURN start_with; END; $$ LANGUAGE plpgsql VOLATILE; 

Use it like this:

 INSERT INTO foo (data) VALUES ('asdf'); -- ERROR: null value in column "a" violates not-null constraint SELECT make_into_serial('foo', 'a'); INSERT INTO foo (data) VALUES ('asdf'); -- OK: 1 row(s) affected 

Answer 4

The answers to this page are outdated . For future reference see this SO answer