如何在PostgreSQL中创建表id字段并为其绑定自定义自增序列

方案实现 #

你只需要在定义id字段时指定默认值为自定义序列的nextval，同时绑定序列的所属关系即可，完整操作如下：

1. 新建表时绑定自定义序列

-- 先创建你需要的自定义序列
CREATE SEQUENCE IF NOT EXISTS accounts_sequence;

-- 建表时指定id字段默认值从自定义序列取值
CREATE TABLE IF NOT EXISTS accounts (
   id INT PRIMARY KEY DEFAULT nextval('accounts_sequence'),
   name VARCHAR(100) NOT NULL
);

-- 绑定序列所属关系，删表时序列会同步删除，避免产生孤儿序列
ALTER SEQUENCE accounts_sequence OWNED BY accounts.id;

2. 已有表修改为使用自定义序列

如果你的accounts表已经创建完成，不想删表重建，可以执行以下语句修改：

-- 确保自定义序列已创建
CREATE SEQUENCE IF NOT EXISTS accounts_sequence;

-- 修改id字段默认值为自定义序列的下一个值
ALTER TABLE accounts ALTER COLUMN id SET DEFAULT nextval('accounts_sequence');

-- 绑定序列所属关系
ALTER SEQUENCE accounts_sequence OWNED BY accounts.id;

-- 可选：如果表中已有历史数据，需要将序列当前值调整为大于现有最大id，避免主键冲突
SELECT setval('accounts_sequence', (SELECT COALESCE(MAX(id),0) FROM accounts));

效果说明 #

配置完成后两个需求都可以满足：

• 需要提前获取id时，直接执行SELECT nextval('accounts_sequence');即可拿到下一个可用id，插入时直接带入使用即可
• 插入语句未指定id字段时，PostgreSQL会自动调用序列的nextval方法赋值，和你手动取id使用的是同一个序列，不会出现取值冲突

补充说明 #

你之前用到的SERIAL类型本质是PostgreSQL的语法糖，内部会自动创建一个accounts_id_seq的隐式序列并绑定默认值，改用自定义序列后直接用INT类型定义id即可，不会产生多余的隐式序列。

内容的提问来源于stack exchange，提问作者kamaci