标签
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