postgreSQL同时将列类型从int更改为bigint [英] postgreSQL concurrently change column type from int to bigint

问题描述

我有一个很大的表(约10亿行)，我需要将id类型从SERIAL更新为BIGSERIAL；猜猜为什么?:).

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"

尽管如此，这将永远锁定我的表并降低我的Web服务.

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)?

推荐答案

如果您没有指向ID的外键，则可以添加新列，填充该列，删除旧列并将新名称重命名为旧列.

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;

这篇关于postgreSQL同时将列类型从int更改为bigint的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！