PostgreSQL10：Identity Columns 特性介绍

发表于 | 分类于 | | 阅读次数:

Identity columns

This is the SQL standard-conforming variant of PostgreSQL is serial
columns. It fixes a few usability issues that serial columns have:

  1. CREATE TABLE / LIKE copies default but refers to same sequence
  2. cannot add/drop serialness with ALTER TABLE
  3. dropping default does not drop sequence
  4. need to grant separate privileges to sequence
  5. other slight weirdnesses because serial is some kind of special macro

identity columns 和 serial 功能很像，是 PostgreSQL 对 SQL 兼容性的提升，并且修复了 serial 类型存在的以下问题：

  1. CREATE TABLE / LIKE 命令复制表时指定相同的序列
  2. 不能使用 ALTER TABLE 增加或删除 serialness
  3. 表删除 default 属性时不会删除序列
  4. 需要对序列进行额外赋权

接下来根据这四种情况进行验证，先来创建两张表，一张基于 serial 表，一张基于 identity columns表。

测试准备

创建两张测试表，创建 serial表

1
2
3
4
5
6
7
8
9
10
11
francs=> 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
18
francs=> 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
francs=> insert into t_serial (name) values ('a'),('b'),('c');
INSERT 0  3
  
francs=>  select  *  from t_serial;
id | name
----+------
 1  | a
 2  | b
 3  | c
(3 rows)
  
francs=> insert into t_identity (name) values('a'),('b'),('c');
INSERT 0  3
  
francs=>  select  *  from t_identity;
id | name
----+------
 1  | a
 2  | b
 3  | c
(3 rows)

备注：两张表同时插入数据，没啥不同。

都使用序列

1
2
3
4
5
6
francs=> \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
11
francs=> 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
20
francs=> 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
14
francs=> 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
17
francs=> 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
2
3
4
5
6
7
8
9
10
11
francs=> ALTER TABLE t_serial ALTER COLUMN id drop default;
ALTER TABLE
  
francs=> \d t_serial_id_seq
Sequence "francs.t_serial_id_seq"
Column  | Type  |  Value
------------+---------+-------
last_value | bigint|  1
log_cnt  | bigint|  0
is_called |  boolean  | f
Owned by: francs.t_serial.id

区别四: Serial 表需要对序列进行额外赋权

创建 readonly 用户并赋权

1
2
3
4
5
6
7
8
9
10
11
12
13
14
postgres=# 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
25
francs=> \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
francs=> \c francs readonly
francs=>  select  *  from francs.t_identity;
id | name
----+------
 1  | a
 2  | b
 3  | c
(3 rows)
  
francs=> insert into francs.t_identity (name) values('4');
INSERT 0  1

备注：identity 表不需要额外给序列赋权，减少了维护成本。

参考

最后推荐和张文升共同编写的《PostgreSQL实战》，本书基于PostgreSQL 10 编写，共18章，重点介绍SQL高级特性、并行查询、分区表、物理复制、逻辑复制、备份恢复、高可用、性能优化、PostGIS等，涵盖大量实战用例！

购买链接：https://item.jd.com/12405774.html

PostgreSQL实战
感谢支持！
0%