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

查看序列的定义

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

复现语句

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

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;