欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

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

程序员文章站 2023-11-02 19:32:40
PostgreSQL10版本中的自增列代码实例分析 [root@pgserver ~]# su - postgres Last login: Sun Apr 22 23:4...

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