Label

PostgreSQL, 10, Features, Identification, Self-Increasing, Coverage, SQL Server IDENTITY Compatibility, SQL Standard

background

Self-increment is a common function of database. There are two very simple ways to implement the self-increment of PostgreSQL before the release of version 10.

1. serial type, automatically create a sequence, while the column is set to INT, the default value is set to nextval('sequence').

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 type, automatically create a sequence, and set the column to INT8, the default value to nextval('sequence').

create table test(id serial8, info text);  

3. Sequence + default value is set to sequence.

create sequence seq1;  
  
create table test (id int default nextval('seq1'), info text);  

To be compatible with SQL Server or SQL standards, PostgreSQL 10 adds support for IDENTITY columns. In fact, the effect is similar, all in order to generate default values.

But IDENTITY has added a new feature that allows users to choose whether to override the default values of this column.

PostgreSQL IDENTITY Column Syntax

1. Create the IDENTITY column.

In the create table grammar, the identity column is defined after the column type using the following grammar.

ALWAYS, which represents self-increment generated by preferential use of system columns.

BY DEFAULT, which indicates that the value entered by the user is preferred.

When using COPY to import data, the input value will forcibly override the setting of IDENTITY. Whether using always or 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.  

Example

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

In fact, identify columns also use sequences, as follows:

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;  
//Error: The sequence test1_id_seq cannot be deleted because the table test1 field ID needs it  
HINT:  You can also delete tables test1 field id replace.  

2. Insertion, how to override default values or user-supplied values.

When the identity column is defined as GENERATED ALWAYS AS IDENTITY, OVERRIDING SYSTEM VALUE is required to override the values generated by the system, otherwise an error will occur.

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.  

When the identity column is defined as GENERATED BY DEFAULT AS IDENTITY, OVERRIDING USER VALUE is required to use the system-generated value (that is, to override the user-submitted value), otherwise the user-submitted value is used.

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.  

Example:

1. Covering IDENTITY column, the system automatically generates self-increment.

OVERRIDING SYSTEM VALUE

postgres=# insert into test (id, info) values (1,'test');  
//Error: 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. Overlay the value provided by the user.

postgres=# Insert into test 1 values (1,'test'); -- User input values take precedence  
INSERT 0 1  
postgres=# Insert in test 1 (id, info) OVERRIDING user VALUE values (1000,'test'); Override user input values (self-increment defined using system columns)  
INSERT 0 1  
postgres=# select * from test1;  
 id | info   
----+------  
  1 | test  
  1 | test  
(2 rows)  

3. COPY, regardless of always or by default, always uses user-provided values.

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)  

Summary

Now you should know that there are several ways to define self-incremental columns in PostgreSQL.

1. serial or serial 8.

2. Identification column definition.

Reference resources

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