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)
);
Implicit insertion
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;
Explicit Insert
insert into myschema.test_seq (id,name) values (5,'ddd');
select * from test_seq;
Implicit insert again
-- can insert normally
insert into myschema.test_seq (name) values ('eee');
--insert fails with duplicate primary key because the sequence itself is incremental and doesn't care about the data being inserted explicitly in the table
insert into myschema.test_seq (name) values ('fff');
--reset the maximum value of the sequence
select setval('myschema.seq_1',(select max(id) from myschema.test_seq)::BIGINT);
-- The sequence number is not rolled back after the transaction is rolled back
begin;
insert into myschema.test_seq (name) values ('ggg');
rollback;
-- After truncate table, the sequence is not affected
truncate table myschema.test_seq;
--reset the sequence
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;
-- explicit insert, can be executed
insert into myschema.test_serial(id,name) values (5,'ccc');
--implicit insert again, the second time will report an error, because if implicitly inserted, serial will do self-increment based on the Id before the explicit insert, and serial will not be aware of the maximum value that currently exists
insert into myschema.test_serial(name) values ('xxx');
insert into myschema.test_serial(name) values ('yyy');
select * from myschema.test_serial;
--truncate table after serial will not reset
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;
--Verify if the transaction will be rolled back with the transaction, conclusion: no
begin;
insert into myschema.test_serial(name) values ('yyyy');
rollback;
--reset serial, it should be noted that the reset Id must be greater than the maximum Id of the relevant table field, otherwise it will generate a duplicate number
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;
--Explicit insertion of values, if defined as generated always as identity then explicit insertion is not allowed unless overriding system value is added
-- Once prompted for the overriding system value, you can
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;
-- explicitly insert values, if defined as generated by default as identity then explicit insertion is allowed.
insert into myschema.test_identiy_2(id,name) values (5,'ccc');
-- After the explicit insertion, continue the implicit insertion, the second insertion will report an error, identity has long been the maximum value after the explicit insertion in the table is not recognized
insert into myschema.test_identiy_2(name) values ('xxx');
insert into myschema.test_identiy_2(name) values ('yyy');
select * from myschema.test_identiy_2;
In short an identity is bullshit, you define it as always as identity, plus overriding system value can be explicitly not inserted
Defined as generated by default as identity also allows explicit insertion
Anyway, since it allows explicit insertion, what's the point of overriding system value?
--truncate and insert again, the self-incremented column will not be reset
truncate table myschema.test_identiy_1;
select * from myschema.test_identiy_1;
begin;
insert into myschema.test_identiy_1(name) values ('xxx');
rollback;
--truncate and reset the self-incrementing column after RESTART IDENTITY
TRUNCATE table myschema.test_identiy_1 RESTART IDENTITY;
select * from myschema.test_identiy_1
--identity self-incrementing table or change
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>