postgreSQL concurrently change column type from int to bigint

Question

I have a pretty big table (around 1 billion rows), and I need to update the id type from SERIAL to BIGSERIAL; guess why?:).

Basically this could be done with this command:

execute "ALTER TABLE my_table ALTER COLUMN id SET DATA TYPE bigint" 

Nevertheless that would lock my table forever and put my web service down.

Is there a quite simple way of doing this operation concurrently (whatever the time it will take)?

Answer 1

If you don't have foreign keys pointing your id you could add new column, fill it, drop old one and rename new to old:

alter table my_table add column new_id bigint;  begin; update my_table set new_id = id where id between 0 and 100000; commit; begin; update my_table set new_id = id where id between 100001 and 200000; commit; begin; update my_table set new_id = id where id between 200001 and 300000; commit; begin; update my_table set new_id = id where id between 300001 and 400000; commit; ...  create unique index my_table_pk_idx on my_table(new_id);  begin; alter table my_table drop constraint my_table_pk; alter table my_table alter column new_id set default nextval('my_table_id_seq'::regclass); update my_table set new_id = id where new_id is null; alter table my_table add constraint my_table_pk primary key using index my_table_pk_idx; alter table my_table drop column id; alter table my_table rename column new_id to id; commit;