PostgreSQL中SERIAL与IDENTITY自增主键的实现差异及操作对比

1. 使用SERIAL创建自增主键 #

执行以下DDL语句创建表：

CREATE TABLE public.test_change_column (
    id SERIAL PRIMARY KEY NOT NULL
)

PostgreSQL会自动生成名为test_change_column_id_seq的序列，并将id列的默认值设置为nextval('test_change_column_id_seq'::regclass)。

2. 使用IDENTITY创建自增主键 #

执行以下DDL语句创建表：

CREATE TABLE public.test_change_column (
    id INTEGER PRIMARY KEY NOT NULL GENERATED ALWAYS AS IDENTITY,
)

此时PostgreSQL同样会生成序列，但不会为id列设置nextval默认值，而是将is_identity字段标记为"YES"。

3. 后期添加自增属性 #

• 若初始未设置自增，无法通过以下语句为id列添加SERIAL类型自增：

  ALTER TABLE public.test_change_column  ALTER COLUMN id TYPE serial; 
  

  执行该语句会报错：type "serial" does not exist。
• 使用IDENTITY方式可通过以下语句添加自增属性：

  ALTER TABLE public.test_change_column ALTER COLUMN id ADD GENERATED BY DEFAULT AS IDENTITY;
  

4. 查看元数据 #

可通过以下SQL查看两种方式的元数据存储差异：

SELECT column_name, column_default, is_identity 
FROM information_schema.columns 
WHERE table_name = 'test_change_column';

5. 删除自增属性 #

• 对于SERIAL创建的自增，需执行ALTER COLUMN id DROP DEFAULT，但此操作不会删除关联的序列表。
• 对于IDENTITY创建的自增，需执行ALTER COLUMN id DROP IDENTITY，此操作会同时删除关联的序列表。

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