How to convert integer to serial

Question

I have a table which has a primary key column "gid" and its type is "Integer NOT NULL". I want to convert it into "Serial NOT NULL" so that I can insert some values into this table. I used following commands to convert it into serial:

CREATE SEQUENCE test_table_gid_seq
        INCREMENT 1
        MINVALUE 1
        MAXVALUE 2147483648 START 1
        CACHE 1;

ALTER TABLE test_table ALTER COLUMN gid
        SET DEFAULT nextval('test_table_gid_seq'::regclass);

This command converted integer to serial. But while I entered some data to the table following error occurred:

ERROR: duplicate key value violates unique constraint "pk_test".

Please help me to solve this. Is there any other method to convert integer to serial?

Possible duplicate of [Adding 'serial' to existing column in Postgres](http://stackoverflow.com/questions/9490014/adding-serial-to-existing-column-in-postgres) — kryger, Jan 21 '16 at 22:22

score 28 · Answer 1 · answered Sep 25 '12 at 05:53

28

Select max value of gid column (max_gid):

SELECT max(gid) FROM test_table;

And use it as start point for sequence (probably, max_gid+1):

ALTER SEQUENCE test_table_gid_seq RESTART WITH max_gid;

answered Sep 25 '12 at 05:53

drnextgis

2,194
1
25
32

still not easy wrapper? – jangorecki Jan 08 '16 at 19:40

How to convert integer to serial

1 Answers1

Linked