Generate Column Generate Column Generate Column is an identifier COLUMN specified in CREATE TABLE. This column will be attached to a hidden sequence and will be inserted with the default hidden sequence as the data row when the data is inserted. The default constraint for this column is NOT NULL. This column has an optional GENERATED {ALWAYS | BY DEFAULT} AS IDENTITY [(sequence_options)] key. If you use the ALWAYS keyword, accept the OVERRIDING SYSTEM VALUES statement when using the INSERT statement. If BY DEFAULT is specified, the value specified BY the user takes precedence.

This feature, introduced in PostgreSQL V10, is a type of constraint that essentially automatically aspoints a unique value to a column. It is similar to an integer sequence with a non-null constraint, but can be inserted in a user-specified manner.

Grammar column data types GENERATED {ALWAYS | BY DEFAULT} AS the IDENTIFY [(sequence_option)]

Grammar explanation:

Data type: usually is a kind of integer [int2 | int4 | int8 | smallint | int | bigint]

GENERATED ALWAYS: PostgreSQL will ALWAYS generate a unique value for the columns and will report an error if you attempt to INSERT or UPDATE data on the GENERATED ALWAYS AS Identify column with an INSERT or UPDATE.

Generated BY Default: PostgreSQL creates an identity column, but if an attempt is made to INSERT or UPDATE the column, PostgreSQL will replace the system GENERATED value with the specified value.

PostgreSQL allows multiple identity columns in a table, and the SEQUENCE object is used internally in the database

Use the sample

GENERATED ALWAYS

Create a table

postgres=# CREATE TABLE tab_product
(
    id int generated always as identity,
    product_name varchar(80),
    product_date date,
    product_vendor varchar(80)
);
CREATE TABLE

Insert data

postgres=# INSERT INTO TAB_PRODUCT ( PRODUCT_NAME , PRODUCT_DATE , PRODUCT_VENDOR)
VALUES ( 'HuaWei' ,
         '2021-01-01' ,
         'HuaweiTec') ;
INSERT 0 1

View the data

postgres=# SELECT * FROM tab_product;
 id | product_name | product_date | product_vendor 
----+--------------+--------------+----------------
  1 | HuaWei       | 2021-01-01   | HuaweiTec
(1 row)

Using the user specified ID will report an error

If the user specifies a custom value in an INSERT or UPDATE statement under the GENERATED ALWAYS constraint, an error will be reported. As follows:

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.

Use the OverRiding System Value statement as follows:

postgres=# INSERT INTO tab_product                                                  
OVERRIDING SYSTEM VALUE                                                   
VALUES(2,
       'Mate',
       '2021-02-03',
       'HuaWeiTec');
INSERT 0 1

Check the data again

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

Create a table

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

Insert data

postgres=# INSERT INTO tab_product ( PRODUCT_NAME , PRODUCT_DATE , PRODUCT_VENDOR)
VALUES ( 'HuaWei' ,
         '2021-01-01' ,
         'HuaweiTec') ;
INSERT 0 1

The user-specified value was inserted in the GENERATED BY DEFAULT AS IDENTITY statement. The value of the user-specified value was inserted AS follows:

postgres=# INSERT INTO tab_product
VALUES(2,
       'Mate',
       '2021-02-03',
       'HuaWeiTec');
INSERT 0 1

View the data

postgres=# SELECT * FROM tab_product;
 id | product_name | product_date | product_vendor 
----+--------------+--------------+----------------
  2 | Mate         | 2021-02-03   | HuaWeiTec
(1 row)

Optional sequence parameter

Because the SEQUENCE object is used internally by Generated AS Identity, parameters used in the SEQUENCE can be specified at the column level. If you specify a starting and step size, a maximum, a minimum, a loop, a cache, etc., you can do the following:

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

Insert data

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

View the data

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)

As you can see, the second data inserted is incremented by the parameters of the specified sequence.

Modify the identity column

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) | | |

Removes the IDENTITY attribute for the column in the 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) | | | 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) | | |

As you can see, after removing the IDENTITY attribute that the column in the table has, the constraint for that column is just NOT NULL.