PostgreSQL 10如何为现有表添加IDENTITY列以替代SERIAL？

当然可以给现有表添加IDENTITY列！先解决你遇到的语法报错问题——你的语句没写完，PostgreSQL 10要求IDENTITY列的语法必须完整指定AS IDENTITY，所以报错是因为你写到GENERATED BY DEFAULT I就中断了。

给现有表添加IDENTITY列的正确语法 #

你可以直接用ALTER TABLE语句新增IDENTITY列，注意完整的语法格式：

1. 使用GENERATED BY DEFAULT（允许手动插入值） #

这种模式下，如果你手动给列赋值，PostgreSQL会接受；如果不赋值，会自动生成自增值：

ALTER TABLE sourceTable 
ADD COLUMN ogc_fid INTEGER GENERATED BY DEFAULT AS IDENTITY;

2. 使用GENERATED ALWAYS（严格自增，需特殊语法手动赋值） #

这种模式下，默认不允许手动插入值，必须使用OVERRIDING SYSTEM VALUE子句才能手动指定值，适合需要严格控制自增逻辑的场景：

ALTER TABLE sourceTable 
ADD COLUMN ogc_fid INTEGER GENERATED ALWAYS AS IDENTITY;

将现有SERIAL列转换为IDENTITY列的步骤 #

SERIAL是PostgreSQL的特有语法糖，本质是自动创建序列+列默认值绑定序列+序列归属列的组合。要把SERIAL列转换成标准的IDENTITY列，按以下步骤操作即可：

1. 确认SERIAL列关联的序列 #

先查询列的默认值，找到对应的序列名称：

SELECT column_default 
FROM information_schema.columns 
WHERE table_name = 'sourceTable' AND column_name = 'ogc_fid';

输出会类似nextval('sourcetable_ogc_fid_seq'::regclass)，这里的sourcetable_ogc_fid_seq就是该列绑定的序列名。

2. 转换列为IDENTITY类型 #

使用ALTER TABLE修改列属性，同时保留现有数据：

-- 转换为BY DEFAULT模式
ALTER TABLE sourceTable 
ALTER COLUMN ogc_fid SET GENERATED BY DEFAULT AS IDENTITY 
USING ogc_fid;

-- 或者转换为ALWAYS模式
ALTER TABLE sourceTable 
ALTER COLUMN ogc_fid SET GENERATED ALWAYS AS IDENTITY 
USING ogc_fid;

USING ogc_fid子句用于保留列中已有的数据，避免转换过程中丢失数据。

3. 验证转换结果（可选） #

可以查询系统表确认序列现在归属于IDENTITY列：

SELECT 
  relname AS sequence_name, 
  seqrelid::regclass AS owned_column
FROM pg_sequence 
WHERE seqrelid = (
  SELECT c.attrelid 
  FROM pg_class c 
  JOIN pg_attribute a ON c.oid = a.attrelid 
  WHERE c.relname = 'sourceTable' AND a.attname = 'ogc_fid'
);

补充说明：IDENTITY列是SQL标准语法，相比PostgreSQL特有的SERIAL，它的行为更规范，也更容易在不同数据库之间迁移，所以推荐优先使用IDENTITY列替代SERIAL。

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