- Sequence
测试用例
- create sequence seq_1 INCREMENT BY 1 MINVALUE 1 START WITH 1;
- create table test_seq
- (
- id int not null default nextval('seq_1') primary key,
- name varchar(10)
- );
隐式插入
- insert into test_seq (name) values ('aaa');
- insert into test_seq (name) values ('bbb');
- insert into test_seq (name) values ('ccc');
显式插入
- insert into test_seq (id,name) values (5,'ddd');
- select * from test_seq;
- test1=# select * from test_seq;
- id |name
- ----+------
- 1 |aaa
- 2 |bbb
- 3 |ccc
- 5 |ddd
再次隐式插入
- --可以正常插入
- insert into test_seq (name)values ('eee');
- --插入失败,主键重复,因为序列自身是递增的,不会关心表中被显式插入的数据
- insert into test_seq (name)values ('fff');
- test1=# insert into test_seq(name) values ('eee');
- INSERT 0 1
- test1=# insert into test_seq(name) values ('fff');
- 错误: 重复键违反唯一约束"test_seq_pkey"
- 描述: 键值"(id)=(5)" 已经存在
- --再次执行语句可正常插入,序列因为之前的错误调用自动增加
- test1=# insert into test_seq(name) values ('fff');
- INSERT 0 1
- test1=# select * from test_seq;
- id | name
- ----+------
- 1 | aaa
- 2 | bbb
- 3 | ccc
- 5 | ddd
- 4 | eee
- 6 | fff
- --重置序列的最大值
- select setval(' seq_1',(select max(id) from test_seq)::BIGINT);
- --事务回滚后,序列号并不会回滚
- test1=# begin;
- BEGIN
- test1=# insert into test_seq(name) values ('ggg');
- INSERT 0 1
- test1=# rollback;
- ROLLBACK
- test1=#
- test1=#
- test1=# select * from test_seq;
- id | name
- ----+------
- 1 | aaa
- 2 | bbb
- 3 | ccc
- 5 | ddd
- 4 | eee
- 6 | fff
- (6 行记录)
- test1=# insert into test_seq(name) values ('ggg');
- INSERT 0 1
- test1=# select * from test_seq;
- id | name
- ----+------
- 1 | aaa
- 2 | bbb
- 3 | ccc
- 5 | ddd
- 4 | eee
- 6 | fff
- 8 | ggg
- (7 行记录)
- -- truncate 表之后,序列不受影响
- test1=# truncate table test_seq;
- TRUNCATE TABLE
- test1=# insert into test_seq(name) values ('ggg');
- INSERT 0 1
- test1=# select * fromtest_seq;
- id | name
- ----+------
- 9 | ggg
- (1 行记录)
- --重置序列
- ALTER SEQUENCE seq_1 RESTART WITH 1;
- test1=# ALTER SEQUENCE seq_1RESTART WITH 1;
- ALTER SEQUENCE
- test1=# insert into test_seq(name) values ('ggg');
- INSERT 0 1
- test1=# select * fromtest_seq;
- id | name
- ----+------
- 9 | ggg
- 1 | ggg
- Serial
测试用例
- create table test_serial
- (
- id serial primary key,
- name varchar(100)
- );
隐式插入
- insert into test_serial(name) values ('aaa');
- insert into test_serial(name) values ('bbb');
- insert into test_serial(name) values ('ccc');
显示插入
- insert into test_serial(id,name) values (5,'ddd);
- select * from test_serial;
- --再次隐式插入,第二次会报错
- insert into test_serial(name) values ('eee');
- insert into test_serial(name) values('fff');
- test1=# insert into test_serial(id,name) values (5,'ddd);
- INSERT 0 1
- test1=# insert into test_serial(name) values ('eee');
- INSERT 0 1
- test1=# insert into test_serial(name) values ('fff');
- 错误: 重复键违反唯一约束"test_serial_pkey"
- 描述: 键值"(id)=(5)" 已经存在
- --再次执行语句可正常插入,序列因为之前的错误调用自动增加
- test1=# insert into test_serial(name)values ('fff');
- INSERT 0 1
- test1=# select * from test_serial;
- id |name
- ----+------
- 1 |aaa
- 2 |bbb
- 3 |ccc
- 5 |ddd
- 4 |eee
- 6 |fff
- (6 行记录)
- --重置serial
- SELECT SETVAL((SELECT sys_get_serial_sequence(' test_serial','id')), 1, false);
- Identity
Identity是R6版本新增的语法,R3数据库不支持该语法。
identity定义成generated by default as identity允许显式插入,
identity定义成always as identity 不允许显示插入,但是加上overriding system value也可以显式插入。
测试用例1
- create table test_identiy_1
- (
- id int generated always as identity (START WITH 1 INCREMENT BY 1) primary key ,
- name varchar(100)
- );
- insert into test_identiy_1(name) values ('aaa');
- insert into test_identiy_1(name) values ('bbb');
- insert into test_identiy_1(name) values ('ccc');
- --显式插入值
- 如果定义为generated always as identity则不允许显式插入,除非增加overriding system value 提示。
- insert into test_identiy_1(id,name) values (5,'ccc');
- insert into test_identiy_1(id,name)overriding system value values (5,'ccc');
- test1=# insert into test_identiy_1(id,name)values (5,'ccc');
- 错误: 无法插入到列"id"
- 描述: 列"id"是定义为GENERATED ALWAYS的标识列.
- 提示: 使用OVERRIDING SYSTEM VALUE覆盖.
- test1=# insert intotest_identiy_1(id,name)overriding system value values (5,'ccc');
- INSERT 0 1
- create table test_identiy_2
- (
- id int generated by default as identity (START WITH 1 INCREMENT BY1) primary key ,
- name varchar(100)
- );
- insert into test_identiy_2(name) values ('aaa');
- insert into test_identiy_2(name) values ('bbb');
- insert into test_identiy_2(name) values ('ccc');
- test1=# insert into test_identiy_2(id,name)values (5,'ccc');
- INSERT 0 1
重置Identity的方式有2种:
- 1. ALTER TABLE test_identiy_1ALTER COLUMN id RESTART WITH 100;
- 2. TRUNCATE table test_identiy_1RESTART IDENTITY;