KingbaseES 自增列三种方式

KingbaseES中3种自增长类型sequence，serial，identity的区别：

对象	显示插入	显示插入后更新最大值	清空表后是否重置	是否跟事务一起回滚	多对象共享	支持重置	出现错误后序列值是否增长
sequence	是	否	否	否	是	是	是
serial	是	否	否	否	否	是	是
identity	是	否	否	否	否	是	是

1、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');

再次隐式插入
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

重置序列

--重置序列的最大值
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

-- truncate 表之后，序列不受影响
test1=# truncate table  test_seq;
TRUNCATE TABLE
test1=# insert into  test_seq (name) values ('ggg');
INSERT 0 1
test1=#  select * from test_seq;
 id | name 
----+------
  9 | ggg
(1 行记录)

--重置序列
ALTER SEQUENCE seq_1 RESTART WITH 1;
test1=# ALTER SEQUENCE  seq_1 RESTART WITH 1;
ALTER SEQUENCE
test1=# insert into  test_seq (name) values ('ggg');
INSERT 0 1
test1=#  select * from test_seq;                    
 id | name 
----+------
  9 | ggg
  1 | ggg

2、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;

--再次隐式插入,第二次会报错
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

--重置serial
SELECT SETVAL((SELECT sys_get_serial_sequence(' test_serial', 'id')), 1, false);

3、Identity

Identity是R6版本新增的语法，R3数据库不支持该语法。

identity定义成generated by default as identity允许显式插入，

identity定义成always as identity 不允许显示插入，但是加上overriding system value也可以显式插入。

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 提示。
test1=# insert into test_identiy_1(id,name) values (5,'ccc');
错误:  无法插入到列"id"
描述:  列"id"是定义为GENERATED ALWAYS的标识列.
提示:  使用OVERRIDING SYSTEM VALUE覆盖.
test1=# insert into test_identiy_1(id,name)overriding system value values (5,'ccc');
INSERT 0 1

generate by default:

create table  test_identiy_2
(
    id int generated by default as identity (START WITH 1 INCREMENT BY 1)  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　　

重置Identity的方式有2种：
1.	ALTER TABLE test_identiy_1 ALTER COLUMN id RESTART WITH 100;
2.	TRUNCATE table test_identiy_1 RESTART IDENTITY;