注册 登录  
 加关注
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

PostgreSQL 中文网

 
 
 

日志

 
 

PostgreSQL10:Identity columns 特性介绍  

2017-06-15 08:37:32|  分类: Postgres基础 |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |

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 类型存在的以下问题:

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

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

创建两张测试表

--创建 serial表
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 表
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
 
--插入数据测试
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)
备注:两张表同时插入数据,没啥不同。

--都使用序列
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 表
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 类型表
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 属性
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 属性
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 属性时不会删除序列
这部分上面已经演示过了。
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 用户并赋权                  
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 表
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 表
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 表不需要额外给序列赋权,减少了维护成本。


参考

  评论这张
 
阅读(71)| 评论(0)
推荐 转载

历史上的今天

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2017