Changing primary key int type to serial

postgresql primary-key auto-increment
MarisP picture MarisP · May 10, 2014 · Viewed 28.6k times · Source

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.

Answer

Joachim Isaksson picture Joachim Isaksson · May 10, 2014

Converting an int to a serial more or less only means adding a sequence default to the value, so to make it a serial;

  • Pick a starting value for the serial, greater than any existing value in the table
    SELECT MAX(id)+1 FROM mytable

  • Create a sequence for the serial (tablename_columnname_seq is a good name)
    CREATE SEQUENCE test_id_seq MINVALUE 3 (assuming you want to start at 3)

  • Alter the default of the column to use the sequence
    ALTER TABLE test ALTER id SET DEFAULT nextval('test_id_seq')

  • Alter the sequence to be owned by the table/column;
    ALTER SEQUENCE test_id_seq OWNED BY test.id

A very simple SQLfiddle demo.

And as always, make a habit of running a full backup before running altering SQL queries from random people on the Internet ;-)

Related questions

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

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

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