PostgreSQL三种自增列sequence,serial,identity的用法区别

---------------------------------------------------------sequence-------------------------------------------------------------

create sequence myschema.seq_1 INCREMENT BY 1 MINVALUE 1 START WITH 1;

create table myschema.test_seq

(

  id int not null default nextval( 'myschema.seq_1' ) primary key ,

  name varchar (10)

);

隐式插入

insert into myschema.test_seq ( name ) values ( 'aaa' );

insert into myschema.test_seq ( name ) values ( 'bbb' );

insert into myschema.test_seq ( name ) values ( 'ccc' );

select * from myschema.test_seq;

显式插入

insert into myschema.test_seq (id, name ) values (5, 'ddd' );

select * from test_seq;

再次隐式插入

--可以正常插入

insert into myschema.test_seq ( name ) values ( 'eee' );

--插入失败，主键重复，因为序列自身是递增的，不会关心表中被显式插入的数据

insert into myschema.test_seq ( name ) values ( 'fff' );

--重置序列的最大值

select setval( 'myschema.seq_1' ,( select max (id) from myschema.test_seq):: BIGINT );

--事务回滚后，序列号并不会回滚

begin ;

insert into myschema.test_seq ( name ) values ( 'ggg' );

rollback ;

-- truncate 表之后，序列不受影响

truncate table myschema.test_seq;

--重置序列

ALTER SEQUENCE myschema.seq_1 RESTART WITH 1;

---------------------------------------------------------serial-------------------------------------------------------------

create table myschema.test_serial

(

  id serial primary key ,

  name varchar (100)

)

select * from test_serial;

insert into myschema.test_serial( name ) values ( 'aaa' );

insert into myschema.test_serial( name ) values ( 'bbb' );

insert into myschema.test_serial( name ) values ( 'ccc' );

select * from myschema.test_serial;

--显式插入,可以执行

insert into myschema.test_serial(id, name ) values (5, 'ccc' );

--再次隐式插入,第二次会报错，因为隐式插入的话，serial会基于显式插入之前的Id做自增，serial无法意识到当前已经存在的最大值

insert into myschema.test_serial( name ) values ( 'xxx' );

insert into myschema.test_serial( name ) values ( 'yyy' );

select * from myschema.test_serial;

--truncate table 后serial不会重置

truncate table myschema.test_serial;

insert into myschema.test_serial( name ) values ( 'aaa' );

insert into myschema.test_serial( name ) values ( 'bbb' );

insert into myschema.test_serial( name ) values ( 'ccc' );

select * from myschema.test_serial;

--验证是否会随着事务一起回滚，结论：不会

begin ;

insert into myschema.test_serial( name ) values ( 'yyy' );

rollback ;

--重置serial，需要注意的是重置的Id必须要大于相关表的字段最大Id，否则会产生重号

SELECT SETVAL(( SELECT pg_get_serial_sequence( 'myschema.test_serial' , 'id' )), 1, false );

---------------------------------------------------------identity-------------------------------------------------------------

drop table myschema.test_identiy_1

create table myschema.test_identiy_1

(

  id int generated always as identity (cache 100 START WITH 1 INCREMENT BY 1) primary key ,

  name varchar (100)

);

create table myschema.test_identiy_2

(

  id int generated by default as identity (cache 100 START WITH 1 INCREMENT BY 1) primary key ,

  name varchar (100)

);

insert into myschema.test_identiy_1( name ) values ( 'aaa' );

insert into myschema.test_identiy_1( name ) values ( 'bbb' );

insert into myschema.test_identiy_1( name ) values ( 'ccc' );

insert into myschema.test_identiy_2( name ) values ( 'aaa' );

insert into myschema.test_identiy_2( name ) values ( 'bbb' );

insert into myschema.test_identiy_2( name ) values ( 'ccc' );

select * from myschema.test_identiy_1;

--显式插入值,如果定义为generated always as identity则不允许显式插入，除非增加overriding system value 提示

--一旦提示了overriding system value，可以

insert into myschema.test_identiy_1(id, name ) values (5, 'ccc' );

insert into myschema.test_identiy_1(id, name )overriding system value values (5, 'ccc' );

select * from myschema.test_identiy_2;

--显式插入值,如果定义为generated by default as identity则允许显式插入，

insert into myschema.test_identiy_2(id, name ) values (5, 'ccc' );

--显式插入后，继续隐式插入,第二次插入会报错，identity已久是不识别表中显式插入后的最大值

insert into myschema.test_identiy_2( name ) values ( 'xxx' );

insert into myschema.test_identiy_2( name ) values ( 'yyy' );

select * from myschema.test_identiy_2;

总之个identity很扯淡，你定义成always as identity，加上overriding system value可以显式不插入

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

不管怎么样，既然都允许显式插入，那扯什么淡的来个overriding system value

--truncate后再次插入，自增列不会重置

truncate table myschema.test_identiy_1;

select * from myschema.test_identiy_1;

begin ;

insert into myschema.test_identiy_1( name ) values ( 'xxx' );

rollback ;

--truncate并且RESTART IDENTITY后，会重置自增列

TRUNCATE table myschema.test_identiy_1 RESTART IDENTITY;

select * from myschema.test_identiy_1

--identity自增列的重置表或者更改

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

<id name = "id" >

   <generator class= "sequence" >

   <param name = "sequence" >adminuser</param>

   </generator>

  </id>

/**

  * @hibernate.id generator-class= "sequence"

  * @hibernate.generator-param name = "sequence" value= "adminuser"

  */

  private int id;