什么是 GENERATED COLUMN
GENERATE COLUMN 是一个在 CREATE TABLE 时指定的标识列(特征列)。该列将会附着一个隐藏的序列，并且在插入数据时以默认的隐藏序列为数据行进行插入。该列默认约束为 NOT NULL 约束。该列后有可选 GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY [ ( sequence _ options ) ]关键字。如果使用 ALWAYS 关键字，当在使用 INSERT 语句时接受 OVERRIDING SYSTEM VALUES 语句。如果指定 BY DEFAULT ，则用户指定的值优先。
该特性是在 PostgreSQL V10 版本中推出，属于约束的一种，实际上是自动为列分配一个唯一的值。类似整形序列加非空约束，但是又可以以用户指定的方式进行数据插入。
语法
列名 数据类型 GENERATED { ALWAYS | BY DEFAULT } AS IDENTIFY [ ( sequence_option ) ]
语法解释:
数据类型: 通常是整型的一种[ int2 | int4 | int8 | smallint | int | bigint ]
GENERATED ALWAYS: PostgreSQL 总会为列生成一个唯一的值，如果尝试以 INSERT 或者 UPDATE 在 GENERATED ALWAYS AS IDENTIFY 列上进行插入数据或者更新时， PostgreSQL 将会报错。
GENERATED BY DEFAULT: PostgreSQL 将会生成一个标识列，但是如果尝试以 INSERT 或者 UPDATE 对该列进行插入或者更新时，PostgreSQL 将会使用指定的值来替代系统生成的值。
PostgreSQL 允许在一个表中有多个标识列，GENERATED AS IDENTIFY 在数据库内部使用的是 SEQUENCE 对象
使用示例
GENERATED ALWAYS
创建表
postgres=# CREATE TABLE tab_product
(
id int generated always as identity,
product_name varchar(80),
product_date date,
product_vendor varchar(80)
);
CREATE TABLE
插入数据
postgres=# INSERT INTO TAB_PRODUCT ( PRODUCT_NAME , PRODUCT_DATE , PRODUCT_VENDOR)
VALUES ( 'HuaWei' ,
'2021-01-01' ,
'HuaweiTec') ;
INSERT 0 1
查看数据
postgres=# SELECT * FROM tab_product;
id | product_name | product_date | product_vendor
----+--------------+--------------+----------------
1 | HuaWei | 2021-01-01 | HuaweiTec
(1 row)
使用用户指定id将会报错
如果在 GENERATED ALWAYS 约束下，用户在 INSERT 或者 UPDATE 语句中指定自定义值将会报错。如下：
postgres=# INSERT INTO tab_product
VALUES(2,
'Mate',
'2021-02-03',
'HuaWeiTec');
ERROR: cannot insert into column "id"
DETAIL: Column "id" is an identity column defined as GENERATED ALWAYS.
HINT: Use OVERRIDING SYSTEM VALUE to override.
解决方法
在语句中使用 OVERRIDING SYSTEM VALUE 语句，如下：
postgres=# INSERT INTO tab_product
OVERRIDING SYSTEM VALUE
VALUES(2,
'Mate',
'2021-02-03',
'HuaWeiTec');
INSERT 0 1
再次查看数据
postgres=# SELECT * FROM tab_product;
id | product_name | product_date | product_vendor
----+--------------+--------------+----------------
1 | HuaWei | 2021-01-01 | HuaweiTec
2 | Mate | 2021-02-03 | HuaWeiTec
(2 rows)
GENERATED BY DEFAULT AS IDENTITY
创建表
postgres=# DROP TABLE IF EXISTS tab_product;
DROP TABLE
postgres=# CREATE TABLE tab_product
(
id int generated by default as identity,
product_name varchar(80),
product_date date,
product_vendor varchar(80)
);
CREATE TABLE
插入数据
postgres=# INSERT INTO tab_product ( PRODUCT_NAME , PRODUCT_DATE , PRODUCT_VENDOR)
VALUES ( 'HuaWei' ,
'2021-01-01' ,
'HuaweiTec') ;
INSERT 0 1
用户指定数据插入
在GENERATED BY DEFAULT AS IDENTITY语句中，用户指定值将会正常运行，如下：
postgres=# INSERT INTO tab_product
VALUES(2,
'Mate',
'2021-02-03',
'HuaWeiTec');
INSERT 0 1
查看数据
postgres=# SELECT * FROM tab_product;
id | product_name | product_date | product_vendor
----+--------------+--------------+----------------
2 | Mate | 2021-02-03 | HuaWeiTec
(1 row)
可选序列参数
由于 GENERATED AS IDENTITY 内部使用 SEQUENCE 对象，因此，可以在列级别指定序列中使用的参数。如指定起始值和步长值，最大值，最小值，是否可以循环，是否有缓存等等，如下：
postgres=# DROP TABLE IF EXISTS tab_product;
DROP TABLE
postgres=# CREATE TABLE tab_product
(
id int generated by default as identity
(start with 1 increment by 2 minvalue 1 maxvalue 10 cycle),
product_name varchar(80),
product_date date,
product_vendor varchar(80)
);
CREATE TABLE
插入数据
postgres=# INSERT INTO tab_product ( PRODUCT_NAME , PRODUCT_DATE , PRODUCT_VENDOR)
VALUES ( 'HuaWei' ,
'2021-01-01' ,
'HuaweiTec') ;
INSERT 0 1
postgres=# INSERT INTO tab_product ( PRODUCT_NAME , PRODUCT_DATE , PRODUCT_VENDOR)
VALUES ( 'Mate' ,
'2021-03-01' ,
'HuaweiTec') ;
INSERT 0 1
查看数据
postgres=# SELECT * FROM tab_product;
id | product_name | product_date | product_vendor
----+--------------+--------------+----------------
1 | HuaWei | 2021-01-01 | HuaweiTec
3 | Mate | 2021-03-01 | HuaweiTec
(2 rows)
可以看到，插入的第二条数据以指定的序列的参数递增。
修改 identity 列
postgres=# \d tab_product;
Table "public.tab_product"
Column | Type | Collation | Nullable | Default
----------------+-----------------------+-----------+----------+----------------------------------
id | integer | | not null | generated by default as identity
product_name | character varying(80) | | |
product_date | date | | |
product_vendor | character varying(80) | | |
postgres=# ALTER TABLE tab_product
ALTER COLUMN id SET GENERATED ALWAYS;
ALTER TABLE
postgres=# \d tab_product
Table "public.tab_product"
Column | Type | Collation | Nullable | Default
----------------+-----------------------+-----------+----------+------------------------------
id | integer | | not null | generated always as identity
product_name | character varying(80) | | |
product_date | date | | |
product_vendor | character varying(80) | | |
移除表中列的 IDENTITY 属性
postgres=# \d tab_product
Table "public.tab_product"
Column | Type | Collation | Nullable | Default
----------------+-----------------------+-----------+----------+------------------------------
id | integer | | not null | generated always as identity
product_name | character varying(80) | | |
product_date | date | | |
product_vendor | character varying(80) | | |
postgres=# ALTER TABLE tab_product
postgres-# ALTER COLUMN id
postgres-# DROP IDENTITY IF EXISTS;
ALTER TABLE
postgres=# \d tab_product
Table "public.tab_product"
Column | Type | Collation | Nullable | Default
----------------+-----------------------+-----------+----------+---------
id | integer | | not null |
product_name | character varying(80) | | |
product_date | date | | |
product_vendor | character varying(80) | | |
可以看到，在移除表中列具有的 IDENTITY 属性后，该列的约束仅仅剩下 NOT NULL 。
- 还没有人评论，欢迎说说您的想法！