postgresql序列

一、PostgreSQL中三种自增列sequence，serial，identity区别

这三个对象都可以实现自增，这里从如下几个维度来看看这几个对象有哪些不同，其中功能性上看，大部分特性都是一致的或者类似的。

1，sequence在所有数据库中的性质都一样，它是跟具体的字段不是强绑定的，其特点是支持多个对个对象之间共享。
　sequence作为自增字段值的时候，对表的写入需要另外单独授权sequence（GRANT USAGE ON SEQUENCE test_old_id_seq;）
　sequence类型的字段表，在使用CREATE TABLE new_table LIKE old_table的时候，新表的自增字段会已久指向原始表的sequence
结论：对于自增字段，无特殊需求的情况下，sequence不适合作为“自增列”，作为最最次选。

2，identity本质是为了兼容标准sql中的语法而新加的，修复了一些serial的缺陷，比如无法通过alter table的方式实现增加或者删除serial字段
　　2.1 identity定义成generated by default as identity也允许显式插入，
　　2.2 identity定义成always as identity，加上overriding system value也可以显式不插入
结论：identity是serial的“增强版”，更适合作为“自增列”使用。

3，sequence，serial，identity共同的缺点是在显式插入之后，无法将自增值更新为表中的最大Id，这一点再显式插入的情况下是潜在自增字段Id冲突的
结论：自增列在显式插入之后，一定要手动重置为表的最大Id。

二、序列的一些基本操作

1、创建序列

postgres=# \h create sequence
Command:     CREATE SEQUENCE
Description: define a new sequence generator
Syntax:
CREATE [ TEMPORARY | TEMP ] SEQUENCE [ IF NOT EXISTS ] name
    [ AS data_type ]
    [ INCREMENT [ BY ] increment ]
    [ MINVALUE minvalue | NO MINVALUE ] [ MAXVALUE maxvalue | NO MAXVALUE ]
    [ START [ WITH ] start ] [ CACHE cache ] [ [ NO ] CYCLE ]
    [ OWNED BY { table_name.column_name | NONE } ]

URL: https://www.postgresql.org/docs/14/sql-createsequence.html
2、更改序列

postgres=# \h alter sequence
Command:     ALTER SEQUENCE
Description: change the definition of a sequence generator
Syntax:
ALTER SEQUENCE [ IF EXISTS ] name
    [ AS data_type ]
    [ INCREMENT [ BY ] increment ]
    [ MINVALUE minvalue | NO MINVALUE ] [ MAXVALUE maxvalue | NO MAXVALUE ]
    [ START [ WITH ] start ]
    [ RESTART [ [ WITH ] restart ] ]
    [ CACHE cache ] [ [ NO ] CYCLE ]
    [ OWNED BY { table_name.column_name | NONE } ]
ALTER SEQUENCE [ IF EXISTS ] name OWNER TO { new_owner | CURRENT_ROLE | CURRENT_USER | SESSION_USER }
ALTER SEQUENCE [ IF EXISTS ] name RENAME TO new_name
ALTER SEQUENCE [ IF EXISTS ] name SET SCHEMA new_schema

URL: https://www.postgresql.org/docs/14/sql-altersequence.html
3、更改序列相关操作

postgres=# select currval('seq_baoyw');
 currval
---------
      89
(1 row)

## 重置序列步长为2
postgres=# alter sequence seq_baoyw increment by 2;
ALTER SEQUENCE
postgres=# select nextval('seq_baoyw');
 nextval
---------
      91
(1 row)

postgres=# select nextval('seq_baoyw');
 nextval
---------
      93
(1 row)

postgres=# select nextval('seq_baoyw');
 nextval
---------
      95
(1 row)

## 重置序列从5开始
postgres=# alter sequence seq_baoyw restart with 5;
ALTER SEQUENCE
postgres=# select nextval('seq_baoyw');
 nextval
---------
       5
(1 row)

postgres=# select nextval('seq_baoyw');
 nextval
---------
       7
(1 row)

postgres=# select nextval('seq_baoyw');
 nextval
---------
       9
(1 row)

##更改序列最大值为200
postgres=# alter sequence seq_baoyw maxvalue 200;
ALTER SEQUENCE
postgres=# select nextval('seq_baoyw');
 nextval
---------
      11
(1 row)

postgres=# select nextval('seq_baoyw');
 nextval
---------
      13
(1 row)

## 更改序列步长为50
postgres=# alter sequence seq_baoyw increment by 50;
ALTER SEQUENCE
postgres=# select nextval('seq_baoyw');
 nextval
---------
      63
(1 row)

postgres=# select nextval('seq_baoyw');
 nextval
---------
     113
(1 row)

postgres=# select nextval('seq_baoyw');
 nextval
---------
     163
(1 row)

postgres=# select nextval('seq_baoyw');
2023-05-15 15:22:32.929 CST [7492] ERROR:  nextval: reached maximum value of sequence "seq_baoyw" (200)
2023-05-15 15:22:32.929 CST [7492] STATEMENT:  select nextval('seq_baoyw');
ERROR:  nextval: reached maximum value of sequence "seq_baoyw" (200)
postgres=#
## 开启序列循环
postgres=# alter sequence seq_baoyw cycle;
ALTER SEQUENCE
postgres=# select nextval('seq_baoyw');
 nextval
---------
       1
(1 row)

postgres=# select nextval('seq_baoyw');
 nextval
---------
      51
(1 row)

postgres=# select nextval('seq_baoyw');
 nextval
---------
     101
(1 row)

postgres=# select nextval('seq_baoyw');
 nextval
---------
     151
(1 row)

postgres=# select nextval('seq_baoyw');
 nextval
---------
       1
(1 row)

postgres=# select nextval('seq_baoyw');
 nextval
---------
      51
(1 row)

## 关闭序列循环
postgres=# alter sequence seq_baoyw no cycle;
ALTER SEQUENCE
postgres=# select nextval('seq_baoyw');
 nextval
---------
     101
(1 row)

postgres=# select nextval('seq_baoyw');
 nextval
---------
     151
(1 row)

postgres=# select nextval('seq_baoyw');
2023-05-15 15:26:56.204 CST [7492] ERROR:  nextval: reached maximum value of sequence "seq_baoyw" (200)
2023-05-15 15:26:56.204 CST [7492] STATEMENT:  select nextval('seq_baoyw');
ERROR:  nextval: reached maximum value of sequence "seq_baoyw" (200)

#更改序列步长
alter sequence seq_baoyw increment by 2;
#重置序列从5开始
alter sequence seq_baoyw restart with 5;
#更改序列最大值为200
alter sequence seq_baoyw maxvalue 200;
#开启序列循环
alter sequence seq_baoyw cycle;
#关闭序列循环
alter sequence seq_baoyw no cyvle;