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