Identity columns
This is the SQL standard-conforming variant of PostgreSQL is 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
identity columns 和 serial 功能很像,是 PostgreSQL 对 SQL 兼容性的提升,并且修复了 serial 类型存在的以下问题:
- CREATE TABLE / LIKE 命令复制表时指定相同的序列
- 不能使用 ALTER TABLE 增加或删除 serialness
- 表删除 default 属性时不会删除序列
- 需要对序列进行额外赋权
接下来根据这四种情况进行验证,先来创建两张表,一张基于 serial 表,一张基于 identity columns表。
测试准备
创建两张测试表,创建 serial表1
2
3
4
5
6
7
8
9
10
11francs=> create table t_serial(id serial primary key, name text);
CREATE TABLE
francs=> \d t_serial
Table "francs.t_serial"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+--------------------------------------
id | integer | | not null | nextval('t_serial_id_seq'::regclass)
name | text| | |
Indexes:
"t_serial_pkey" PRIMARY KEY, btree (id)
创建 identity 表1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18francs=> create table t_identity(id int8 generated by default AS IDENTITY primary key, name text);
CREATE TABLE
francs=> \d t_identity
Table "francs.t_identity"
Column | Type | Collation | Nullable | Default
--------+--------+-----------+----------+----------------------------------
id | bigint | | not null | generated by default as identity
name | text | | |
Indexes:
"t_identity_pkey" PRIMARY KEY, btree (id)
francs=> \ds
List of relations
Schema | Name | Type | Owner
--------+----------------------+----------+--------
francs | t_identity_id_seq| sequence | francs
francs | t_serial_id_seq| sequence | francs
插入数据测试1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21 insert into t_serial (name) values ('a'),('b'),('c');
INSERT 0 3
select * from t_serial;
id | name
----+------
1 | a
2 | b
3 | c
(3 rows)
insert into t_identity (name) values('a'),('b'),('c');
INSERT 0 3
select * from t_identity;
id | name
----+------
1 | a
2 | b
3 | c
(3 rows)
备注:两张表同时插入数据,没啥不同。
都使用序列1
2
3
4
5
6francs=> \ds
List of relations
Schema | Name | Type | Owner
--------+----------------------+----------+--------
francs | t_identity_id_seq| sequence | francs
francs | t_serial_id_seq| sequence | francs
区别一: Serial 表通过CREATE TABLE / LIKE 命令复制表时指定相同的序列
通过 CREATE TABLE LIKE 命令复制一张 serial 表1
2
3
4
5
6
7
8
9
10
11francs=> create table t_serial2 (like t_serial including all);
CREATE TABLE
francs=> \d t_serial2
Table "francs.t_serial2"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+--------------------------------------
id | integer | | not null | nextval('t_serial_id_seq'::regclass)
name | text| | |
Indexes:
"t_serial2_pkey" PRIMARY KEY, btree (id)
备注:可见 t_serial2 表也是用的 t_serial 的序列。
复制一张 identity 类型表1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20francs=> create table t_identity2 ( like t_identity including all);
CREATE TABLE
francs=> \d t_identity2
Table "francs.t_identity2"
Column | Type | Collation | Nullable | Default
--------+--------+-----------+----------+----------------------------------
id | bigint | | not null | generated by default as identity
name | text | | |
Indexes:
"t_identity2_pkey" PRIMARY KEY, btree (id)
francs=> \d+ t_identity2_id_seq
Sequence "francs.t_identity2_id_seq"
Column | Type | Value | Storage
------------+---------+-------+---------
last_value | bigint| 1 | plain
log_cnt | bigint| 0 | plain
is_called | boolean | f | plain
Sequence for identity column: francs.t_identity2.id
备注:新复制的表 t_identity2 使用新建的序列 t_identity2_id_seq,而serial 表复制表后仍然使用老序列,到这里就很清楚了。
区别二: Serial 表不能使用 ALTER TABLE 增加或删除 serialness
serial 表需要分两步删除 serial 属性1
2
3
4
5
6
7
8
9
10
11
12
13
14francs=> ALTER TABLE t_serial ALTER COLUMN id drop default;
ALTER TABLE
francs=> drop sequence t_serial_id_seq;
DROP SEQUENCE
francs=> \d t_serial
Table "francs.t_serial"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------
id | integer | | not null |
name | text| | |
Indexes:
"t_serial_pkey" PRIMARY KEY, btree (id)
identity 表可通过 ALTER TABLE 一条命令删除字段 IDENTITY 属性1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17francs=> ALTER TABLE t_identity ALTER COLUMN id DROP IDENTITY;
ALTER TABLE
francs=> \d t_identity
Table "francs.t_identity"
Column | Type | Collation | Nullable | Default
--------+--------+-----------+----------+---------
id | bigint | | not null |
name | text | | |
Indexes:
"t_identity_pkey" PRIMARY KEY, btree (id)
francs=> insert into t_identity(name) values('4');
ERROR: null value in column "id" violate
francs=> \d t_identity_id_seq
Did not find any relation named "t_identity_id_seq".
备注:删除 t_identity 表 id 字段 IDENTITY 属性的同时,序列 t_identity_id_seq 也被删除了。
区别三: Serial 表表删除 default 属性时不会删除序列
这部分上面已经演示过了。
1 | francs=> ALTER TABLE t_serial ALTER COLUMN id drop default; |
区别四: Serial 表需要对序列进行额外赋权
创建 readonly 用户并赋权1
2
3
4
5
6
7
8
9
10
11
12
13
14postgres=# create role readonly login encrypted password 'readonly';
CREATE ROLE
postgres=# \c francs francs
You are now connected to database "francs" as user "francs".
francs=> grant connect on database francs to readonly;
GRANT
francs=> grant usage on schema francs to readonly;
GRANT
francs=> grant select,insert on t_serial,t_identity to readonly;
GRANT
t_serial 表1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25francs=> \c francs readonly
You are now connected to database "francs" as user "readonly".
francs=> select * from francs.t_serial;
id | name
----+------
1 | a
2 | b
3 | c
(3 rows)
francs=> insert into francs.t_serial (name) values('4');
ERROR: permission denied for sequence t_serial_id_seq
提示没有权限,序列赋权后执行正常。
francs=> \c francs francs
You are now connected to database "francs" as user "francs".
francs=> grant usage on SEQUENCE t_serial_id_seq to readonly;
GRANT
francs=> \c francs readonly
You are now connected to database "francs" as user "readonly".
francs=> insert into francs.t_serial (name) values('4');
INSERT 0 1
t_identity 表1
2
3
4
5
6
7
8
9
10
11 \c francs readonly
select * from francs.t_identity;
id | name
----+------
1 | a
2 | b
3 | c
(3 rows)
insert into francs.t_identity (name) values('4');
INSERT 0 1
备注:identity 表不需要额外给序列赋权,减少了维护成本。