no zuo no die系列，来自于pg的wiki。 这一节的内容是：不要使用serial。 理由是: >The serial types have some weird behaviors that make schema, dependency, and permission management unnecessarily cumbersome. 该类型有某些行为会给模式、依赖和权限管理带来不必要的麻烦。 **基本用法** ``` [local]:5432 pg12@testdb=# drop table if exists t_serial; DROP TABLE Time: 158.910 ms [local]:5432 pg12@testdb=# CREATE TABLE t_serial ( pg12@testdb(# id serial PRIMARY KEY, pg12@testdb(# c1 varchar pg12@testdb(# ); CREATE TABLE Time: 9.424 ms [local]:5432 pg12@testdb=# [local]:5432 pg12@testdb=# INSERT INTO t_serial (c1) VALUES ('a'), ('b'), ('c') RETURNING *; id | c1 ----+---- 1 | a 2 | b 3 | c (3 rows) INSERT 0 3 Time: 3.076 ms [local]:5432 pg12@testdb=# [local]:5432 pg12@testdb=# select * from t_serial; id | c1 ----+---- 1 | a 2 | b 3 | c (3 rows) Time: 0.847 ms [local]:5432 pg12@testdb=# ``` serial与GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY的作用很相似 ``` [local]:5432 pg12@testdb=# CREATE TABLE t_identify ( pg12@testdb(# id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, pg12@testdb(# c1 text pg12@testdb(# ); CREATE TABLE Time: 5.215 ms [local]:5432 pg12@testdb=# [local]:5432 pg12@testdb=# INSERT INTO t_identify (c1) VALUES ('a'), ('b'), ('c') RETURNING *; id | c1 ----+---- 1 | a 2 | b 3 | c (3 rows) INSERT 0 3 Time: 1.127 ms [local]:5432 pg12@testdb=# ``` 实际上，serial符合SQL标准具备兼容性，而GENERATED BY DEFAULT AS IDENTITY是PG的语法不具备兼容性。 **权限** serial类型的第一个问题是与serial列相关的sequence需要单独处理 ``` [local]:5432 pg12@testdb=# drop user if exists user1029; NOTICE: role "user1029" does not exist, skipping DROP ROLE Time: 0.422 ms [local]:5432 pg12@testdb=# CREATE USER user1029 with password 'test'; CREATE ROLE Time: 0.543 ms [local]:5432 pg12@testdb=# GRANT INSERT ON t_serial TO user1029; GRANT Time: 1.297 ms [local]:5432 pg12@testdb=# GRANT INSERT ON t_identify TO user1029; GRANT Time: 3.729 ms [local]:5432 pg12@testdb=# SET SESSION AUTHORIZATION user1029; SET Time: 1.243 ms [local]:5432 user1029@testdb=> INSERT INTO t_serial (c1) VALUES ('d'); ERROR: permission denied for sequence t_serial_id_seq Time: 2.705 ms [local]:5432 user1029@testdb=> INSERT INTO t_identify (c1) VALUES ('d'); INSERT 0 1 Time: 3.340 ms [local]:5432 user1029@testdb=> ``` 可以看到，类型serial的实现底层依赖于sequence，id列对应的sequence是t_serial_id_seq。 而GENERATED BY DEFAULT AS IDENTITY则不需要依赖，因此执行不会出错。 通过授权可以解决此问题 ``` -- pg12 [local]:5432 pg12@testdb=# GRANT USAGE ON SEQUENCE t_serial_id_seq to user1029; GRANT Time: 5.291 ms [local]:5432 pg12@testdb=# -- user1029 [local]:5432 user1029@testdb=> INSERT INTO t_serial (c1) VALUES ('d'); INSERT 0 1 Time: 3.791 ms [local]:5432 user1029@testdb=> ``` 由于serial类型依赖于sequence，如果我们对sequence进行相关操作，那会出现什么情况？ ``` [local]:5432 pg12@testdb=# drop sequence t_serial_id_seq; ERROR: cannot drop sequence t_serial_id_seq because other objects depend on it DETAIL: default value for column id of table t_serial depends on sequence t_serial_id_seq HINT: Use DROP ... CASCADE to drop the dependent objects too. Time: 1.056 ms ``` 存在依赖，删除时会报错，添加cascade选项。 ``` [local]:5432 pg12@testdb=# drop sequence t_serial_id_seq cascade; NOTICE: drop cascades to default value for column id of table t_serial DROP SEQUENCE Time: 10.075 ms [local]:5432 pg12@testdb=# \d t_serial Table "public.t_serial" Column | Type | Collation | Nullable | Default --------+-------------------+-----------+----------+--------- id | integer | | not null | c1 | character varying | | | Indexes: "t_serial_pkey" PRIMARY KEY, btree (id) [local]:5432 pg12@testdb=# ``` t_serial列变成了普通的int字段。 **虽然有些不足，但还是可以用的** ``` Identity columns This is the SQL standard-conforming variant of PostgreSQL's serial columns. It fixes a few usability issues that serial columns have: - CREATE TABLE / LIKE copies default but refers to same sequence - cannot add/drop serialness with ALTER TABLE - dropping default does not drop sequence - need to grant separate privileges to sequence - other slight weirdnesses because serial is some kind of special macro ``` **参考资料** [Don't Do This](https://wiki.postgresql.org/wiki/Don't_Do_This) [PostgreSQL 10 identity columns explained](https://www.2ndquadrant.com/en/blog/postgresql-10-identity-columns/)