PostgreSQL中的GENERATED COLUMN




  • 什么是 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=<span class="hljs-meta"># CREATE TABLE tab_product</span>
    (
    id <span class="hljs-keyword">int</span> generated always <span class="hljs-keyword">as</span> identity,
    <span class="hljs-function">product_name <span class="hljs-title">varchar</span>(<span class="hljs-params"><span class="hljs-number">80</span></span>),
    product_date date,
    product_vendor <span class="hljs-title">varchar</span>(<span class="hljs-params"><span class="hljs-number">80</span></span>)
    )</span>;
    CREATE TABLE

    插入数据

    postgres=<span class="hljs-comment"># INSERT INTO TAB_PRODUCT ( PRODUCT_NAME , PRODUCT_DATE , PRODUCT_VENDOR)</span>
    VALUES ( <span class="hljs-string">'HuaWei'</span> ,
    <span class="hljs-string">'2021-01-01'</span> ,
    <span class="hljs-string">'HuaweiTec'</span>) ;
    INSERT 0 1

    查看数据

    postgres=<span class="hljs-comment"># SELECT * FROM tab_product;</span>
    id <span class="hljs-params">| product_name |</span> product_date <span class="hljs-params">| product_vendor
    ----+--------------+--------------+----------------
    1 |</span> HuaWei       <span class="hljs-params">| 2021-01-01   |</span> HuaweiTec
    (<span class="hljs-number">1</span> row)

    使用用户指定id将会报错

        如果在 GENERATED ALWAYS 约束下,用户在 INSERT 或者 UPDATE 语句中指定自定义值将会报错。如下:

    postgres=<span class="hljs-comment"># INSERT INTO tab_product</span>
    VALUES(2,
    'Mate',
    '2021-02-03',
    'HuaWeiTec');
    ERROR:  cannot <span class="hljs-keyword">insert</span> <span class="hljs-keyword">into</span> <span class="hljs-keyword">column</span> <span class="hljs-string">"id"</span>
    DETAIL:  <span class="hljs-keyword">Column</span> <span class="hljs-string">"id"</span> <span class="hljs-keyword">is</span> an <span class="hljs-keyword">identity</span> <span class="hljs-keyword">column</span> defined <span class="hljs-keyword">as</span> <span class="hljs-keyword">GENERATED</span> ALWAYS.
    HINT:  <span class="hljs-keyword">Use</span> <span class="hljs-keyword">OVERRIDING</span> <span class="hljs-keyword">SYSTEM</span> <span class="hljs-keyword">VALUE</span> <span class="hljs-keyword">to</span> 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=<span class="hljs-comment"># DROP TABLE IF EXISTS tab_product;</span>
    <span class="hljs-keyword">DROP</span> <span class="hljs-keyword">TABLE</span>
    postgres=<span class="hljs-comment"># CREATE TABLE tab_product</span>
    (
    <span class="hljs-keyword">id</span> <span class="hljs-built_in">int</span> <span class="hljs-keyword">generated</span> <span class="hljs-keyword">by</span> <span class="hljs-keyword">default</span> <span class="hljs-keyword">as</span> <span class="hljs-keyword">identity</span>,
    product_name <span class="hljs-built_in">varchar</span>(<span class="hljs-number">80</span>),
    product_date <span class="hljs-built_in">date</span>,
    product_vendor <span class="hljs-built_in">varchar</span>(<span class="hljs-number">80</span>)
    );
    <span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">TABLE</span>

    插入数据

    postgres=<span class="hljs-comment"># INSERT INTO tab_product ( PRODUCT_NAME , PRODUCT_DATE , PRODUCT_VENDOR)</span>
    VALUES ( <span class="hljs-string">'HuaWei'</span> ,
    <span class="hljs-string">'2021-01-01'</span> ,
    <span class="hljs-string">'HuaweiTec'</span>) ;
    INSERT 0 1

    用户指定数据插入
    在 GENERATED BY DEFAULT AS IDENTITY 语句中,用户指定值将会正常运行,如下:

    postgres=<span class="hljs-comment"># INSERT INTO tab_product</span>
    VALUES(2,
    <span class="hljs-string">'Mate'</span>,
    <span class="hljs-string">'2021-02-03'</span>,
    <span class="hljs-string">'HuaWeiTec'</span>);
    INSERT 0 1

    查看数据

    postgres=<span class="hljs-comment"># SELECT * FROM tab_product;</span>
    id <span class="hljs-params">| product_name |</span> product_date <span class="hljs-params">| product_vendor
    ----+--------------+--------------+----------------
    2 |</span> Mate         <span class="hljs-params">| 2021-02-03   |</span> HuaWeiTec
    (<span class="hljs-number">1</span> row)
    • 可选序列参数由于 GENERATED AS IDENTITY 内部使用 SEQUENCE 对象,因此,可以在列级别指定序列中使用的参数。如指定起始值和步长值,最大值,最小值,是否可以循环,是否有缓存等等,如下:
    postgres=<span class="hljs-comment"># DROP TABLE IF EXISTS tab_product;</span>
    <span class="hljs-keyword">DROP</span> <span class="hljs-keyword">TABLE</span>
    postgres=<span class="hljs-comment"># CREATE TABLE tab_product</span>
    (
    <span class="hljs-keyword">id</span> <span class="hljs-built_in">int</span> <span class="hljs-keyword">generated</span> <span class="hljs-keyword">by</span> <span class="hljs-keyword">default</span> <span class="hljs-keyword">as</span> <span class="hljs-keyword">identity</span>
    (<span class="hljs-keyword">start</span> <span class="hljs-keyword">with</span> <span class="hljs-number">1</span> <span class="hljs-keyword">increment</span> <span class="hljs-keyword">by</span> <span class="hljs-number">2</span> <span class="hljs-keyword">minvalue</span> <span class="hljs-number">1</span> maxvalue <span class="hljs-number">10</span> <span class="hljs-keyword">cycle</span>),
    product_name <span class="hljs-built_in">varchar</span>(<span class="hljs-number">80</span>),
    product_date <span class="hljs-built_in">date</span>,
    product_vendor <span class="hljs-built_in">varchar</span>(<span class="hljs-number">80</span>)
    );
    <span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">TABLE</span>

    插入数据

    postgres=<span class="hljs-comment"># INSERT INTO tab_product ( PRODUCT_NAME , PRODUCT_DATE , PRODUCT_VENDOR)</span>
    VALUES ( 'HuaWei' ,
    '2021-01-01' ,
    'HuaweiTec') ;
    <span class="hljs-keyword">INSERT</span> <span class="hljs-number">0</span> <span class="hljs-number">1</span>
    postgres=<span class="hljs-comment"># INSERT INTO tab_product ( PRODUCT_NAME , PRODUCT_DATE , PRODUCT_VENDOR)</span>
    <span class="hljs-keyword">VALUES</span> ( <span class="hljs-string">'Mate'</span> ,
    <span class="hljs-string">'2021-03-01'</span> ,
    <span class="hljs-string">'HuaweiTec'</span>) ;
    <span class="hljs-keyword">INSERT</span> <span class="hljs-number">0</span> <span class="hljs-number">1</span>

    查看数据

    postgres=<span class="hljs-comment"># SELECT * FROM tab_product;</span>
    id <span class="hljs-params">| product_name |</span> product_date <span class="hljs-params">| product_vendor
    ----+--------------+--------------+----------------
    1 |</span> HuaWei       <span class="hljs-params">| 2021-01-01   |</span> HuaweiTec
    <span class="hljs-number">3</span> <span class="hljs-params">| Mate         |</span> <span class="hljs-number">2021</span>-<span class="hljs-number">03</span>-<span class="hljs-number">01</span>   <span class="hljs-params">| HuaweiTec
    (2 rows)</span>

    可以看到,插入的第二条数据以指定的序列的参数递增。

    修改 identity 列

    postgres=<span class="hljs-comment"># \d tab_product;</span>
    Table "public.tab_product"
    Column     |         Type          | Collation | Nullable |             Default
    <span class="hljs-comment">----------------+-----------------------+-----------+----------+----------------------------------</span>
    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=<span class="hljs-comment"># \d tab_product</span>
    Table <span class="hljs-string">"public.tab_product"</span>
    Column     <span class="hljs-params">|         Type          |</span> Collation <span class="hljs-params">| Nullable |</span>           Default
    ----------------+-----------------------+-----------+----------+------------------------------
    id             <span class="hljs-params">| integer               |</span>           <span class="hljs-params">| <span class="hljs-keyword">not</span> null |</span> generated always as identity
    product_name   <span class="hljs-params">| character varying(80) |</span>           <span class="hljs-params">|          |</span>
    product_date   <span class="hljs-params">| date                  |</span>           <span class="hljs-params">|          |</span>
    product_vendor <span class="hljs-params">| character varying(80) |</span>           <span class="hljs-params">|          |</span>

    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 。

     
     

    Speak Your Mind

    *

    京ICP备14059771号-2