Change Existing Column to Serial in Postgres

by | May 1, 2019


In order to change an existing column in a Postgres table to a serial column where the ID autoincrements, you need to:
1) Create the SEQUENCE that is used behind the scenes.
2) Assign the SEQUENCE to the COLUMN
3) Find the value the SEQUENCE should start from
4) Inform the SEQUENCE to start from that value
5) Test by doing an insert of some dummy data
6) Delete your test data once satisfied

The following is an example from a table in one of my databases:

   Postgres SQL Example of Changing an INT column to a SERIAL

CREATE SEQUENCE pc_product_contact_uid_seq;

alter table pc_product_contact alter column pc_uid set default nextval('pc_product_contact_uid_seq');

select max(pc_uid) from pc_product_contact --- this returns 7076 in my case

ALTER SEQUENCE pc_product_contact_uid_seq
MINVALUE 7077   -- so start one higher than the max I got of 7076
START 7077

-----------Conduct test to see if it is working -----
insert into pc_product_contact (pz_id_fk, pct_id_fk, pc_desc,pc_order)
values('100001','4','test and delete','0');

select * from pz_product_za where pz_uid > 7086

delete from pc_product_contact where pz_id_fk > 8000;