PostgreSQL 中 serial 与 bigserial 类型的理解与转换

1. serial 与 bigserial 类型理解

serial 与 bigserial 在 PostgreSQL 中属于伪类型，实际只在 create table 时可以引用该类型。serial 和 bigserial 类型，实际上在这个字段上做的操作就是：

• 创建了一个 integer (serial) 和 bigint (bigserial) 类型
• 创建一个对应的序列，序列的最大值指定为 integer 和 bigint 的最大值
• 把这个序列和字段关联上

我们可以通过下面表 t1 理解一下上面说的三步：

建表语句

postgres=# create table t1(id serial, bid bigserial, tid int, btid bigint);
CREATE TABLE

postgres=# \d+ t1
                                                       Table "public.t1"
 Column |  Type   | Collation | Nullable |             Default             | Storage | Compression | Stats target | Description
--------+---------+-----------+----------+---------------------------------+---------+-------------+--------------+-------------
 id     | integer |           | not null | nextval('t1_id_seq'::regclass)  | plain   |             |              |
 bid    | bigint  |           | not null | nextval('t1_bid_seq'::regclass) | plain   |             |              |
 tid    | integer |           |          |                                 | plain   |             |              |
 btid   | bigint  |           |          |                                 | plain   |             |              |
Access method: heap
1
2
3
4
5
6
7
8
9
10
11
12

查看序列的定义

postgres=# \d+ t1_id_seq
                     Sequence "public.t1_id_seq"
  Type   | Start | Minimum |  Maximum   | Increment | Cycles? | Cache
---------+-------+---------+------------+-----------+---------+-------
 integer |     1 |       1 | 2147483647 |         1 | no      |     1
Owned by: public.t1.id

postgres=# \d+ t1_tid_seq
                         Sequence "public.t1_bid_seq"
  Type  | Start | Minimum |       Maximum       | Increment | Cycles? | Cache
--------+-------+---------+---------------------+-----------+---------+-------
 bigint |     1 |       1 | 9223372036854775807 |         1 | no      |     1
Owned by: public.t1.bid
1
2
3
4
5
6
7
8
9
10
11
12
13

复现语句

create table t2 (tid bigint not null primary key);
create sequence t2_tid1_seq;
alter table t2 alter column set default nextval('t2_tid1_seq' ::regclass);
1
2
3

2. 转换方法

之所以要聊一聊转换方法，其实是源于一次与研发同事的讨论。研发的同事说可以直接把定义了 serial 字段类型的字段从 int4 (integer) 改为 int8 (bigint)，但是我一直认为这样做是没有意义的，因为按照 PostgreSQL 严谨的作风，序列绝对是有最大值限制的，于是就有了第一部分对于表结构和序列定义的详细调查。

事实证明，有如下几个结论：

• serial 字段的 int4 是可以直接改为 int8 的，并没有严格限制，但是从 int4 改为 int8 并没有什么实际意义，serial 字段仍然不能存储超过 2147483647 条数据
• serial 的序列有最大值 2147483647，并且达到最大值后不允许循环使用
• 如果要将 serial 的存储大小从 int4 变更为更大，只能通过复现语句创建新的 int8 序列，修改原字段类型为 int8 后，重新关联新索引（下面有具体步骤）

将 serial 改为 bigserial 的具体步骤：

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

扩展

PostgreSQL 中支持 3 种自增资端，最大长度分别如下：

• smallserial - 32767
• serial - 2147483647
• bigserial - 9223372036854775807

End ～