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

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);

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

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的方式有2种：

1.	ALTER TABLE test_identiy_1 ALTER COLUMN id RESTART WITH 100;

2.	TRUNCATE table test_identiy_1 RESTART IDENTITY;