1010
PostgreSQL 10 新特性 - identity column (serial, 自增)
標簽
PostgreSQL , 10 , 特性 , identify , 自增 , 覆蓋 , SQL Server IDENTITY兼容 , SQL標準
背景
自增列是數據庫的一個常用功能，PostgreSQL的自增列在10的版本出來前，有兩種非常簡單的方法來實現：
1、serial類型，自動創建一個序列，同時將列設置為INT，默認值設置為nextval('序列')。
create table test(id serial, info text);
postgres=# \d+ test
Table "public.test"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+---------+-----------+----------+----------------------------------+----------+--------------+-------------
id | integer | | not null | nextval('test_id_seq'::regclass) | plain | |
info | text | | | | extended | |
2、serial8類型，，自動創建一個序列，同時將列設置為INT8，默認值設置為nextval('序列')。
create table test(id serial8, info text);
3、序列+默認值設置為序列，
create sequence seq1;
create table test (id int default nextval('seq1'), info text);
為了兼容SQL Server或SQL標準，PostgreSQL 10加入了IDENTITY列的支持。實際上功效類似，都是為了生成默認值。
但是IDENTITY加入了一個新的功能，可以允許用戶選擇是否覆蓋這個列的默認值。
PostgreSQL IDENTITY列語法
1、創建IDENTITY列。
create table語法中，在列的類型後使用如下語法定義identity列。
ALWAYS，表示優先使用係統列生成的自增值。
BY DEFAULT，表示優先使用用戶輸入的值。
使用COPY導入數據時，輸入的值會強行覆蓋IDENTITY的設置。不管使用always還是by default。
GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY [ ( sequence_options ) ]
This clause creates the column as an identity column.
It will have an implicit sequence attached to it and the column
in new rows will automatically have values from the sequence assigned to it.
The clauses ALWAYS and BY DEFAULT determine how the sequence
value is given precedence over a user-specified value in an INSERT statement.
If ALWAYS is specified, a user-specified value is only accepted if the
INSERT statement specifies OVERRIDING SYSTEM VALUE.
If BY DEFAULT is specified, then the user-specified value takes precedence.
See INSERT for details. (In the COPY command, user-specified values are always used regardless of this setting.)
The optional sequence_options clause can be used to override the options of the sequence.
See CREATE SEQUENCE for details.
例子
postgres=# create table test (id int GENERATED ALWAYS AS IDENTITY (cache 100), info text);
CREATE TABLE
postgres=# create table test1 (id int GENERATED BY DEFAULT AS IDENTITY (cache 100), info text);
CREATE TABLE
postgres=# \d test
Table "public.test"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+------------------------------
id | integer | | not null | generated always as identity
info | text | | |
postgres=# \d test1
Table "public.test1"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+----------------------------------
id | integer | | not null | generated by default as identity
info | text | | |
實際上identify列，也使用了序列，如下：
postgres=# \ds
List of relations
Schema | Name | Type | Owner
--------+--------------+----------+----------
public | test1_id_seq | sequence | postgres
public | test_id_seq | sequence | postgres
postgres=# drop sequence test1_id_seq;
錯誤: 無法刪除 序列 test1_id_seq, 因為 表 test1 字段 id 需要它
HINT: 您也可以刪除 表 test1 字段 id 代替.
2、插入，如何覆蓋默認值或覆蓋用戶提供值。
當identity列被定義為GENERATED ALWAYS AS IDENTITY時，如果要覆蓋係統產生的值，需要使用OVERRIDING SYSTEM VALUE，否則會報錯。
OVERRIDING SYSTEM VALUE
Without this clause, it is an error to specify an explicit value
(other than DEFAULT) for an identity column defined as GENERATED ALWAYS.
This clause overrides that restriction.
當identity列被定義為GENERATED BY DEFAULT AS IDENTITY時，如果要使用係統產生的值（即覆蓋用戶提交的值），需要使用OVERRIDING USER VALUE，否則會使用用戶提交的值。
OVERRIDING USER VALUE
If this clause is specified, then any values supplied for
identity columns defined as GENERATED BY DEFAULT are ignored
and the default sequence-generated values are applied.
This clause is useful for example when copying values between tables.
Writing INSERT INTO tbl2 OVERRIDING USER VALUE SELECT * FROM tbl1 will
copy from tbl1 all columns that are not identity columns in tbl2
while values for the identity columns in tbl2 will be generated by the
sequences associated with tbl2.
例子：
1、覆蓋IDENTITY列，係統自動生成的自增值。
OVERRIDING SYSTEM VALUE
postgres=# insert into test (id, info) values (1,'test');
錯誤: cannot insert into column "id"
DETAIL: Column "id" is an identity column defined as GENERATED ALWAYS.
HINT: Use OVERRIDING SYSTEM VALUE to override.
postgres=# insert into test (id, info) OVERRIDING SYSTEM VALUE values (1,'test');
INSERT 0 1
postgres=# select * from test;
id | info
----+------
1 | test
(1 row)
2、覆蓋用戶提供的值。
postgres=# insert into test1 values (1,'test'); -- 用戶輸入的值優先
INSERT 0 1
postgres=# insert into test1 (id, info) OVERRIDING user VALUE values (1000,'test'); -- 覆蓋用戶輸入的值（使用係統列定義的自增值）
INSERT 0 1
postgres=# select * from test1;
id | info
----+------
1 | test
1 | test
(2 rows)
3、COPY，不管always還是by default，總是使用用戶提供的值。
postgres=# copy test from stdin
postgres-# ;
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself, or an EOF signal.
>> 1999 abc
>> 2999 cde
>> \.
COPY 2
postgres=# select * from test;
id | info
------+------
1 | test
1999 | abc
2999 | cde
(3 rows)
小結
現在你應該知道，在PostgreSQL中有幾種定義自增列的方法了吧。
1、serial或serial8類型。
2、identity列定義。
參考
https://www.postgresql.org/docs/10/static/sql-createtable.html
https://www.postgresql.org/docs/10/static/sql-insert.html
https://www.postgresql.org/docs/10/static/sql-createsequence.html
最後更新：2017-11-12 01:34:23