PostgreSQL 修改字段类型从int到bigint

由于现在pg的版本，修改int到bigint仍然需要rewrite表，会导致表阻塞，无法使用。但可以考虑其他方式来做。
此问题是排查现网pg使用序列的情况时遇到的。

由于int的最大值只有21亿左右，而且自增列多为主键，当达到最大值时，数据就会无法插入。一般情况是修改类型为bigint，但直接做会锁表，影响现网使用。

这里分两块来看：

1、分区表（修改序列）：
对于分区表可以直接修改序列为循环形式，而且最大值设置为int的最大值，因为单个分区表很少会将int值用完。

alter sequence seq_name MAXVALUE 2147483647  CYCLE;

注意这里适用于按日或按月分区的表，对于hash分区表，只能修改字段类型。 

 

2、非分区表（修改为bigint）

由于创建表时，可能使用的是serial，所以此时就需要新建一个序列，不然字段id在删除时，之前的序列也会跟着一同被删除。
下面的步骤中要注意，添加主键约束部分，如果new_id上没有not null约束，则此时会进行全表扫描检查有无not null的记录。虽然pg检查记录是否为not null的操作比较快，但这一步还是会锁较长时间（看记录数多少而定）。

alter table table_name add  column new_id bigint;

---循环更新new_id
do language plpgsql $$
declare
i int;
begin
for i in 0..1000 loop
update table_name set new_id = id where id >= min(id)+ (max(id)-min(id))/1000*i and id< min(id) + max(id)-min(id))/1000*(i+1);
end loop;
end $$;

create unique index CONCURRENTLY on table_name(new_id);


BEGIN;
ALTER TABLE table_name DROP CONSTRAINT table_name_pkey;
CREATE SEQUENCE table_name_new_id_seq;
ALTER TABLE table_name ALTER COLUMN new_id SET DEFAULT nextval('table_name_new_id_seq'::regclass);
UPDATE table_name SET new_id = id WHERE new_id IS NULL;
ALTER TABLE table_name ADD CONSTRAINT table_name_pkey PRIMARY KEY USING INDEX table_name_pk_idx;
ALTER TABLE table_name DROP COLUMN id;
ALTER TABLE table_name RENAME COLUMN new_id to id;
ALTER SEQUENCE table_name_new_id_seq RENAME TO table_name_id_seq;
SELECT setval('table_name_id_seq', (SELECT max(id) FROM table_name));
COMMIT;

 

还有可以通过修改数据字典来实现同样操作的，不过不推荐使用，有可能引起元数据损坏。