Migrating PostgreSQL SERIAL Columns to IDENTITY

Jun 19, 2019  

PostgreSQL introduced identity columns in version 10 which offer a number of advantages. As it happens, I had some old SERIAL columns that I wanted to migrate to the new IDENTITY thing but couldn’t find any instructions online. So here’s how you do it at the psql shell. Assuming TABLE a(id SERIAL PRIMARY KEY); with some rows already:

start transaction isolation level serializable;
alter table a alter column id drop default;
select nextval('a_id_seq');  -- pretend it's 368
drop sequence a_id_seq;
alter table a alter column id add generated by default as identity (start with 368);