postgres中的序列及自增量

Posted on

业务中需要调整id字段类型，最开始设置的为SERIAL，后续需要调整至SERIAL8。

1
2
alter table tab_name alter column data_id type SERIAL8;
-- 失败！类型SERIAL8不存在！

但实际上 create table 语句中的字段类型 SERIAL8，并不存在。 SERIAL8 （或者SERIAL）会创建对应的sequence， 然后将sequence 跟表的主键关联。具体对应过程为：

1
2
3
create table table_name (dataId bigint not null primary key);
create sequence seq_name;
alter table table_name alter column set default nextval('seq_name' ::regclass);

理解了这个过程，就容易明白了，直接修改字段类型 SERIAL8 是不可以的，需要按照上述过程操作：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
-- 先把字段类型调整为 int8, 这一步操作耗时较长，在8核心+SSD固态盘的主机上，2亿条数据耗时超过一刻钟
alter table bw_data alter column dataId type int8;
 
-- 创建sequence, 默认bigint
-- 起始值从当前表中选取
select max(dataId) into max_dataId_from_data from bw_data;
create sequence bw_data_dataid_seq8 start with max_dataId_from_data;
 
-- 先将表的自增量主键字段 默认值置为null
alter table bw_data alter column dataId set default null;
 
-- 设置默认值为新的 sequence
alter table bw_data alter column dataId set default nextval('bw_data_dataid_seq8' ::regclass);
 
-- 原来的sequence 可以删除
drop sequence bw_data_dataid_seq;
1
2
3
4
                    Sequence "public.bw_data_dataid_seq8"
  Type  |   Start   | Minimum |       Maximum       | Increment | Cycles? | Cache
--------+-----------+---------+---------------------+-----------+---------+-------
 bigint | 265432104 |       1 | 9223372036854775807 |         1 | no      |     1

参考文章

  1. https://blog.csdn.net/kampoo/article/details/113628598