一、问题场景

在PostgreSQL数据库管理中，经常需要对表的自增列（IDENTITY列）进行管理。例如：

  • 迁移数据库时，需要备份或重建表的自增列配置
  • 批量核查数据库中所有表的自增列设置
  • 需生成标准化的ALTER TABLE语句，用于重现自增列的创建逻辑

本文以实际场景为例，介绍如何识别PostgreSQL中所有IDENTITY自增列，并自动生成对应的ALTER语句。


二、环境说明

  • 数据库：PostgreSQL 10及以上版本（IDENTITY列特性在PostgreSQL 10引入）
  • 操作模式：默认public模式（可根据实际场景调整）
  • 工具：支持PostgreSQL的客户端（如psql、pgAdmin、DBeaver等）


三、处理思路

  1. 识别IDENTITY列：通过系统信息表information_schema.columns筛选出所有IDENTITY列，获取表名、列名等基础信息
  2. 关联序列信息：IDENTITY列依赖PostgreSQL的序列（sequence）实现自增，需关联pg_sequences系统视图获取序列的关键参数（起始值、增量值）
  3. 生成ALTER语句：根据获取的表、列及序列信息，自动拼接ALTER TABLE语句，确保语句可直接用于重建自增列配置


四、处理步骤

步骤1：查询所有IDENTITY列的基础信息

通过information_schema.columns视图筛选public模式下所有IDENTITY列，确认其基本属性：

SELECT 
    table_name,          -- 表名
    column_name,         -- 列名
    column_default,      -- 列默认值（包含关联的序列信息）
    is_identity,         -- 是否为IDENTITY列（'YES'表示是）
    identity_generation  -- IDENTITY生成策略（'BY DEFAULT'或'ALWAYS'）
FROM information_schema.columns
WHERE is_identity = 'YES'
    AND table_schema = 'public'  -- 限定public模式
ORDER BY table_name, ordinal_position;  -- 按表名和列顺序排序

步骤2：分析查询结果

执行上述SQL后，可获得如下关键信息：

  • 哪些表包含IDENTITY列
  • 自增列的列名及关联的序列（通过column_default字段，如nextval('user_temp_i_id_seq'::regclass)）
  • 自增策略（BY DEFAULT表示插入时可手动指定值，ALWAYS表示强制自增）

步骤3：生成对应的ALTER语句

结合pg_sequences视图获取序列的起始值（start_value）和增量值（increment_by），自动生成ALTER TABLE语句：

SELECT
    format(
        'ALTER TABLE %I.%I ALTER COLUMN %I ADD GENERATED BY DEFAULT AS IDENTITY (START WITH %s INCREMENT BY %s);',
        c.table_schema,  -- 表所在模式（此处为public）
        c.table_name,    -- 表名
        c.column_name,   -- IDENTITY列名
        s.start_value,   -- 序列起始值
        s.increment_by   -- 序列增量值
    ) AS identity_alter_sql  -- 生成的ALTER语句
FROM
    information_schema.columns c
-- 关联序列信息，通过序列全名匹配
JOIN
    pg_sequences s
    ON pg_get_serial_sequence(
        format('%I.%I', c.table_schema, c.table_name),  -- 带模式的表名
        c.column_name
    ) = format('%I.%I', s.schemaname, s.sequencename)  -- 带模式的序列名
WHERE
    c.is_identity = 'YES'
    AND c.table_schema = 'public'  -- 限定public模式
ORDER BY
    c.table_schema, c.table_name, c.column_name;

步骤4：验证生成的语句

检查生成的ALTER语句格式是否正确，重点确认：

  • 表名、列名是否准确（特殊名称是否自动添加引号）
  • 起始值（START WITH）和增量值（INCREMENT BY）是否与实际序列一致


五、执行结果

1. IDENTITY列基础信息查询结果

table_name

column_name

column_default

is_identity

identity_generation

user_temp

i_id

nextval('user_temp_i_id_seq'::regclass)

YES

BY DEFAULT

movetest

i_id

nextval('movetest_i_id_seq'::regclass)

YES

BY DEFAULT

2. 生成的ALTER语句结果

ALTER TABLE public.user_temp ALTER COLUMN i_id ADD GENERATED BY DEFAULT AS IDENTITY (START WITH 206 INCREMENT BY 1);
ALTER TABLE public.movetest ALTER COLUMN i_id ADD GENERATED BY DEFAULT AS IDENTITY (START WITH 1 INCREMENT BY 1);


六、总结

通过上述方法，可高效识别PostgreSQL中所有IDENTITY自增列，并自动生成标准化的ALTER语句。该方案适用于数据库迁移、配置备份、批量运维等场景，避免了手动编写语句的繁琐和错误风险。实际使用时，可根据需要调整table_schema参数（如非public模式）或补充序列的其他参数（如最大值、缓存大小）。