PostgreSQL 序列（Sequence）操作笔记

PostgreSQL 序列（Sequence）操作笔记

 

一、序列基础操作

 

1. 创建序列

-- 标准创建语句（从1开始，步长1，无最值限制，缓存1）
CREATE SEQUENCE IF NOT EXISTS 序列名
    START WITH 1          -- 起始值
    INCREMENT BY 1        -- 步长（每次递增1）
    NO MINVALUE           -- 无最小值限制
    NO MAXVALUE           -- 无最大值限制
    CACHE 1;              -- 缓存数（1表示不缓存）
    
--示例
CREATE SEQUENCE IF NOT EXISTS t_100_reassessment_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;

2. 给表字段绑定序列（设置默认值）

-- 为表的id字段设置默认值为序列的下一个值
ALTER TABLE 表名 
ALTER COLUMN 字段名 SET DEFAULT nextval('序列名');

--示例
ALTER TABLE t_100_reassessment 
ALTER COLUMN id SET DEFAULT nextval('t_100_reassessment_id_seq');

3. 将序列归属到指定列（推荐，便于管理）

ALTER SEQUENCE 序列名 OWNED BY 表名.字段名;
--作用：删除表 / 列时可自动清理关联序列，避免孤立序列。

 

二、序列常用查询

 

1. 查看字段绑定的默认序列

 

SELECT column_default
FROM information_schema.columns
WHERE table_name = '表名' AND column_name = '字段名';


--示例（查看 t_100_reassessment 的 id 字段默认序列）：
SELECT column_default
FROM information_schema.columns
WHERE table_name = 't_100_reassessment' AND column_name = 'id';
-- 预期返回：nextval('t_100_reassessment_id_seq'::regclass)

 2. 查询表中最大 ID 值

SELECT MAX(字段名) FROM 表名;

--示例
SELECT MAX(id) FROM t_100_economic_loss_result;

3. 查询序列当前最后分配的值

-- 方式1：直接查序列的last_value
SELECT last_value FROM 序列名;

-- 方式2：查序列详情（含步长、下一个值）
SELECT 
    last_value,                -- 当前最后值
    increment_by,              -- 步长
    last_value + increment_by AS next_value_would_be  -- 预估下一个值
FROM 
    序列名;

-- 方式3：通过系统表pg_sequences查询
SELECT 
    last_value,
    increment_by,
    last_value + increment_by AS next_value_would_be
FROM 
    pg_sequences 
WHERE 
    sequencename = '序列名';
    
--示例
SELECT 
    last_value,
    increment_by,
    last_value + increment_by AS next_value_would_be
FROM 
    t_100_person_mange_id_seq;

4. 查询字段关联的序列名（精准查询） 

SELECT pg_get_serial_sequence('表名', '字段名');

--示例
SELECT pg_get_serial_sequence('t_100_report_record', 'id');

三、序列值修复（核心）

 

1. 问题场景

 

序列的last_value与表中最大 ID 不一致，导致插入数据时主键冲突（duplicate key）。

 

2. 修复语句（将序列值同步为表最大 ID+1）

-- 通用模板（COALESCE处理空值：无数据时设为0+1=1）
SELECT setval('序列名', (SELECT COALESCE(MAX(字段名), 0) + 1 FROM 表名));

-- 简化写法
SELECT setval('序列名', COALESCE((SELECT MAX(字段名) FROM 表名), 0) + 1);

--示例1
-- 同步t_100_influence_field的序列值
SELECT setval('t_100_influence_field_id_seq', (SELECT COALESCE(MAX(id), 0) + 1 FROM t_100_influence_field));

--示例2
-- 同步t_100_person_mange的序列值
SELECT setval('t_100_person_mange_id_seq', COALESCE((SELECT MAX(id) FROM t_100_person_mange), 0) + 1);

 

关键说明：COALESCE(值, 0) 表示如果查询结果为 NULL（表无数据），则取 0，避免计算报错。

 

总结

 

1. 序列绑定字段的完整流程：创建序列 → 设置字段默认值 → 将序列归属到字段，三步缺一不可（归属步骤便于管理）。
2. 序列值与表 ID 不一致是高频问题，核心修复语句为setval(序列名, COALESCE(MAX(id), 0)+1)，需重点掌握。
3. 常用查询可快速定位序列关联关系和当前值，是排查序列问题的基础。