原文地址 https://www.naiyerasif.com/post/2024/09/04/stop-using-serial-in-postgres/
从PG10开始支持identity，用于替代serial。
part1、serial有权限问题
代码语言：txt复制 想象一下：数据库所有者victoria创建如下表：
postgres=# create table events (
id serial primary key,
created_at timestamptz not null default current_timestamp
);
然后，他们将events表的所有权限授予另一个用户gizem。但是当gizem尝试插入或更新一行时，他们收到此错误：
postgres=# insert into events default values;
-- ERROR: permission denied for sequence events_id_seq
解决办法？只需授予生成id列的序列的使用权限即可：
postgres=# grant usage on sequence events_id_seq to gizem;
或者，切换到标识列。如果最后一个id值为 99，则操作如下：
postgres=# drop sequence events_id_seq cascade;
postgres=# alter table events alter column id add generated always as identity (restart 100);part2、serial缺乏完整性保证
代码语言：txt复制 postgres=# create table pings (
id serial primary key,
last_ping timestamptz not null default current_timestamp
);
postgres=# d pings
Table "public.pings"
Column | Type | Collation | Nullable | Default
----------- -------------------------- ----------- ---------- -----------------------------------
id | integer | | not null | nextval('pings_id_seq'::regclass)
last_ping | timestamp with time zone | | not null | CURRENT_TIMESTAMP
Indexes:
"pings_pkey" PRIMARY KEY, btree (id)
插入一行：
insert into pings values (1, default);
该行被添加id为 1。现在尝试这样做：
insert into pings default values;
-- ERROR: duplicate key value violates unique constraint "pings_pkey"
-- DETAIL: Key (id)=(1) already exists.
发生了什么？第一次插入id1 并没有推进序列。Postgres 没有发出任何警告或错误。
现在，让我们用identity尝试做同样的事情：
postgres=# create table pings2 (
id int generated always as identity primary key,
last_ping timestamptz not null default current_timestamp
);
postgres=# d pings2
Table "public.pings2"
Column | Type | Collation | Nullable | Default
----------- -------------------------- ----------- ---------- ------------------------------
id | integer | | not null | generated always as identity
last_ping | timestamp with time zone | | not null | CURRENT_TIMESTAMP
Indexes:
"pings2_pkey" PRIMARY KEY, btree (id)
postgres=# d pings2_id_seq
Sequence "public.pings2_id_seq"
Type | Start | Minimum | Maximum | Increment | Cycles? | Cache
--------- ------- --------- ------------ ----------- --------- -------
integer | 1 | 1 | 2147483647 | 1 | no | 1
Sequence for identity column: public.pings2.id
postgres=# insert into pings2 values (1, now());
-- ERROR: cannot insert a non-DEFAULT value into column "id"
-- DETAIL: Column "id" is an identity column defined as GENERATED ALWAYS.
-- HINT: Use OVERRIDING SYSTEM VALUE to override.
这次您会收到一个友好的错误，解释插入失败的原因。如果要插入可以带上 OVERRIDING SYSTEM VALUE ，例如:
INSERT INTO pings2 (id,last_ping) OVERRIDING SYSTEM VALUE VALUES(1,now());
此外，假如当您误操作将pings_id_seq删除的时候：
postgres=# drop sequence pings_id_seq cascade;
-- NOTICE: drop cascades to default value for column id of table pings
Postgres 只会给你一个通知，但不会阻止你。因此，您最终会得到default从id列。可以d查看到表结构，自增序列没有了
postgres=# d pings
Table "public.pings"
Column | Type | Collation | Nullable | Default
----------- -------------------------- ----------- ---------- -------------------
id | integer | | not null |
last_ping | timestamp with time zone | | not null | CURRENT_TIMESTAMP
Indexes:
"pings_pkey" PRIMARY KEY, btree (id)
这可能不是你想要的主键上的东西。更糟糕的是，您无法恢复id列返回到serial.相反，您需要创建一个新序列，并使用
alter table ... set default.这真是个麻烦！
现在，让我们用 identity 列来尝试一下。
postgres=# drop sequence pings2_id_seq cascade;
ERROR: cannot drop sequence pings2_id_seq because column id of table pings2 requires it
HINT: You can drop column id of table pings2 instead.
可以看到有个error报错，提示在使用中，禁止被删除。part3、serial不是真正的类型，serial当您创建一个包含如下列的表时：
代码语言：txt复制 create table events (
id serial primary key,
created_at timestamptz not null default current_timestamp
);
Postgres 将其解析为下面3条sql：
1、 create sequence events_id_seq as integer;
2、 create table events (
id integer primary key not null default nextval('events_id_seq'::regclass),
created_at timestamp with time zone not null default current_timestamp
);
3、 alter sequence events_id_seq owned by events.id;part4、identity和serial在设置id的时候使用方法类似
代码语言：txt复制 serial的调整方法：
alter sequence events_id_seq restart 100;
identity的调整方法：
alter sequence events2_id_seq restart with 300; 或 alter table events2 alter column id restart with 100;part5、identity和serial在复制表时候的现象不一样
代码语言：txt复制 postgres=# create table return_events (like events including all);
postgres=# d return_events
Table "public.return_events"
Column | Type | Collation | Nullable | Default
------------ -------------------------- ----------- ---------- ------------------------------------
id | integer | | not null | nextval('events_id_seq'::regclass)
created_at | timestamp with time zone | | not null | CURRENT_TIMESTAMP
Indexes:
"return_events_pkey" PRIMARY KEY, btree (id)
可以看到 return_events 表的序列用的是 events_id_seq （和events表共用一个sequence）
postgres=# create table pings2new (like pings2 including all);
postgres=# d pings2new
Table "public.pings2new"
Column | Type | Collation | Nullable | Default
----------- -------------------------- ----------- ---------- ------------------------------
id | integer | | not null | generated always as identity
last_ping | timestamp with time zone | | not null | CURRENT_TIMESTAMP
Indexes:
"pings2new_pkey" PRIMARY KEY, btree (id)
postgres=# ds
List of relations
Schema | Name | Type | Owner
-------- ------------------ ---------- ----------
public | events2_id_seq | sequence | postgres
public | events_id_seq | sequence | postgres
public | pings2_id_seq | sequence | postgres
public | pings2new_id_seq | sequence | postgres
(4 rows)
可以看到 pings2new 表的序列用的是 pings2new_id_seq （和pings2表不会共用一个sequence）
