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


  • 0
    点赞
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值