PostgreSQL10版本中的自增列代码实例分析

PostgreSQL10版本中的自增列代码实例分析

[root@pgserver ~]# su - postgres
Last login: Sun Apr 22 23:46:50 EDT 2018 on pts/2
[postgres@pgserver ~]$ psql
psql (10beta2)
Type "help" for help.

postgres=# create table testa (id int GENERATED ALWAYS AS IDENTITY (cache 100), info text); 
CREATE TABLE
postgres=# \d
              List of relations
 Schema |     Name      |   Type   |  Owner   
--------+---------------+----------+----------
 public | "tab01"       | table    | postgres
 public | lei_two       | table    | postgres
 public | lei_two_2     | table    | postgres
 public | lei_two_22    | table    | postgres
 public | newtable      | table    | postgres
 public | newtbl        | table    | postgres
 public | sampletbl     | table    | postgres
 public | t1            | table    | postgres
 public | tab_datatype1 | table    | postgres
 public | tab_lei       | table    | postgres
 public | tab_lei_1     | table    | postgres
 public | test_t5       | table    | postgres
 public | test_t6       | table    | postgres
 public | testa         | table    | postgres
 public | testa_id_seq  | sequence | postgres
(15 rows)

postgres=# \d testa
                          Table "public.testa"
 Column |  Type   | Collation | Nullable |           Default            
--------+---------+-----------+----------+------------------------------
 id     | integer |           | not null | generated always as identity
 info   | text    |           |          | 

postgres=# insert into testa values('abc');
ERROR:  invalid input syntax for integer: "abc"
LINE 1: insert into testa values('abc');
                                 ^
postgres=# insert into testa(info)  values('abc');
INSERT 0 1
postgres=# 
postgres=# select * from testa;
 id | info 
----+------
  1 | abc
(1 row)

postgres=# insert into testa(info)  values('abcd');
INSERT 0 1
postgres=# select * from testa;
 id | info 
----+------
  1 | abc
  2 | abcd
(2 rows)

postgres=# insert into testa (id, info) OVERRIDING SYSTEM VALUE values (11,'test');  
INSERT 0 1
postgres=# select * from testa;
 id | info 
----+------
  1 | abc
  2 | abcd
 11 | test
(3 rows)

postgres=# insert into testa(info)  values('asdfghjkl');
INSERT 0 1
postgres=# select * from testa;
 id |   info    
----+-----------
  1 | abc
  2 | abcd
 11 | test
  3 | asdfghjkl
(4 rows)

postgres=# insert into testa(info)  values('asdf4');
INSERT 0 1
postgres=# select * from testa;
 id |   info    
----+-----------
  1 | abc
  2 | abcd
 11 | test
  3 | asdfghjkl
  4 | asdf4
(5 rows)

postgres=# insert into testa(info)  values('asdf5');
INSERT 0 1
postgres=# insert into testa(info)  values('asdf6');
INSERT 0 1
postgres=# insert into testa(info)  values('asdf7');
INSERT 0 1
postgres=# insert into testa(info)  values('asdf8');
INSERT 0 1
postgres=# insert into testa(info)  values('asdf9');
INSERT 0 1
postgres=# select * from testa;
 id |   info    
----+-----------
  1 | abc
  2 | abcd
 11 | test
  3 | asdfghjkl
  4 | asdf4
  5 | asdf5
  6 | asdf6
  7 | asdf7
  8 | asdf8
  9 | asdf9
(10 rows)

postgres=# insert into testa(info)  values('asdf10');
INSERT 0 1
postgres=# select * from testa;
 id |   info    
----+-----------
  1 | abc
  2 | abcd
 11 | test
  3 | asdfghjkl
  4 | asdf4
  5 | asdf5
  6 | asdf6
  7 | asdf7
  8 | asdf8
  9 | asdf9
 10 | asdf10
(11 rows)

postgres=# insert into testa(info)  values('asdf11');
INSERT 0 1
postgres=# select * from testa;
 id |   info    
----+-----------
  1 | abc
  2 | abcd
 11 | test
  3 | asdfghjkl
  4 | asdf4
  5 | asdf5
  6 | asdf6
  7 | asdf7
  8 | asdf8
  9 | asdf9
 10 | asdf10
 11 | asdf11
(12 rows)

postgres=# 

从上面可以看到， OVERRIDING SYSTEM VALUE关键字可以插入values子句后面手工指定的值，该手工值可以比identity所在列的所有值都大。

postgres=# insert into testa (id, info) OVERRIDING SYSTEM VALUE values (9,'asdf9');  --->>注意此处的9，本语句执行之前id的最大值是11
INSERT 0 1
postgres=# select * from testa;
 id |   info    
----+-----------
  1 | abc
  2 | abcd
 11 | test
  3 | asdfghjkl
  4 | asdf4
  5 | asdf5
  6 | asdf6
  7 | asdf7
  8 | asdf8
  9 | asdf9
 10 | asdf10
 11 | asdf11
  9 | asdf9
(13 rows)

postgres=# 
从上面可以看到， OVERRIDING SYSTEM VALUE关键字可以插入values子句后面手工指定的值，该手工值可以比identity所在列的最大值还小。

identity列是PG10中的新特性，请见：https://www.postgresql.org/docs/10/static/sql-createtable.html

其实identity列也是使用sequence来实现的

postgres=# \ds+
                           List of relations
 Schema |     Name     |   Type   |  Owner   |    Size    | Description 
--------+--------------+----------+----------+------------+-------------
 public | testa_id_seq | sequence | postgres | 8192 bytes | 
(1 row)


postgres=#