关于数据库:PostgreSQL中的GENERATED-COLUMN

36次阅读

共计 4761 个字符，预计需要花费 12 分钟才能阅读完成。

什么是 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。

正文完
数据库
发表至： 数据库
2021-07-22
 0
关于数据库:舒明稳定支撑日高峰亿级保单交易国泰产险的运维创新实践
关于数据库:OceanBase首次阐述战略继续坚持自研开放之路-开源300万行核心代码
关于数据库:数据库系统概论王珊第十章数据库恢复技术第一二节事务的基本概念和数据库恢复概述
关于数据库:Tapdata-Cloud-场景通关系列数据入湖仓之-MySQL-→-Doris极简架构更实时更简便