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"

尽管如此，这将永远锁定我的桌子并关闭我的网络服务.

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屋！