PostgreSql 序列

一、概述

  在 PostgreSQL 中，序列用于生成唯一标识符，通常用于为表的主键列生成连续的唯一值。若目的仅是为表字段设置自增 id，可考虑序列类型来实现，可参考《PostgreSql 设置自增字段》

二、创建序列

2.1 语法

CREATE [ TEMPORARY | TEMP ] SEQUENCE [ IF NOT EXISTS ] name [ 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 } ]

参数说明：

TEMPORARY or TEMP：如果被指定，只会为这个会话创建序列对象，并且在会话退出时自动删除它。当临时序列存在时，已有的同名永久序列（在这个会话中）会变得不可见，不过可以用模式限定的名称来引用同名永久序列。
IF NOT EXISTS：如果已经存在一个同名的关系时不要抛出错误。这种情况下会发出一个提示。注意这不保证现有的关系与即将创建的序列相似它甚至可能都不是一个序列。
name：要创建的序列的名称（可以是模式限定的）。
data_type：可选的子句 AS data_type 制定序列的数据类型。有效类型是 smallint、integer、和 bigint。默认是 bigint。 数据类型决定了序列的默认最小和最大值。
increment：可选的子句 INCREMENT BY increment 指定为了创建新值会把哪个值加到当前序列值上。一个正值将会创造一个上升序列，负值会创造一个下降序列。默认值是 1。
minvalue|NO MINVALUE：可选的子句 MINVALUE minvalue 决定一个序列能产生的最小值。如果没有提供这个子句或者指定了 NO MINVALUE，那么会使用默认值。升序序列的默认值为1。降序序列的默认值为数据类型的最小值。
maxvalue|NO MAXVALUE：可选的子句 MAXVALUE maxvalue 决定该序列的最大值。如果没有提供这个子句或者指定了 NO MAXVALUE，那么将会使用默认值。升序序列的默认值是数据类型的最大值。降序序列的默认值是 -1。
start：可选的子句 START WITH start 允许序列从任何地方开始。对于上升序列和下降序列来说，默认的开始值分别是 minvalue 和 maxvalue。
cache：可选的子句 CACHE cache 指定要预分配多少个序列数并且把它们放在内存中以便快速访问。最小值为 1 （一次只生成 一个值，即没有缓存），默认值也是 1。
CYCLE|NO CYCLE：对于上升序列和下降序列，CYCLE 选项允许序列 在分别达到 maxvalue 和 minvalue 时回卷。如果达到该限制，下一个产生的数字将分别是 minvalue 和 maxvalue。如果指定了 NO CYCLE，当序列到达其最大值后任何 nextval 调用将返回一个错误。如果 CYCLE 和 NO CYCLE 都没有被指定，则默认为 NO CYCLE。
OWNED BY table_name.column_name|OWNED BY NONE：OWNED BY 选项导致序列被与一个特定的表列关联在一起，这样如果该列（或者整个表）被删除，该序列也将被自动删除。指定的表必须和序列具有相同的拥有者并且在同一个模式中。默认选项 OWNED BY NONE指定该序列不与某个列关联。

常用操作函数：

操作函数	描述
nextval ( regclass ) → bigint	将序列对象推进到下一个值并返回该值。
setval ( regclass, bigint [, boolean ] ) → bigint	设置序列对象的当前值，boolean 为 false 时设置下个序列值。
currval ( regclass ) → bigint	返回 nextval 在当前会话中为该序列最近获取的值。
lastval () → bigint	返回 nextval 在当前会话中最近返回的值。

2.2 示例

--创建序列
postgres=# create sequence seq_t;
CREATE SEQUENCE--插入数据时使用序列
postgres=# insert into t values(nextval('seq_t'),'zhao');
INSERT 0 1
postgres=# insert into t values(nextval('seq_t'),'qian');
INSERT 0 1
postgres=# insert into t values(nextval('seq_t'),'sun');
INSERT 0 1
postgres=# select * from t;id | name
----+------1 | zhao2 | qian3 | sun
(3 rows)--查询序列当前值
postgres=# select currval('seq_t');currval
---------3
(1 row)--更新序列值为下一个
postgres=# select nextval('seq_t');nextval
---------4
(1 row)postgres=# select currval('seq_t');currval
---------4
(1 row)--设置序列为指定值
postgres=# select setval('seq_t',100);setval
--------100
(1 row)postgres=# select currval('seq_t');currval
---------100
(1 row)--建表时字段默认使用序列（通常序列字段设置为主键）
postgres=# create table t(id int default nextval('seq_t')primary key,name varchar);
CREATE TABLE
postgres=# \d tTable "public.t"Column |       Type        | Collation | Nullable |          Default
--------+-------------------+-----------+----------+----------------------------id     | integer           |           | not null | nextval('seq_t'::regclass)name   | character varying |           |          |
Indexes:"t_pkey" PRIMARY KEY, btree (id)

三、修改序列

3.1 语法

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_USER | SESSION_USER }
ALTER SEQUENCE [ IF EXISTS ] name RENAME TO new_name
ALTER SEQUENCE [ IF EXISTS ] name SET SCHEMA new_schema

参数说明：

name：要修改的序列的名称（可以是模式限定的）。
IF EXISTS：在序列不存在时不要抛出一个错误。这种情况下会发出一个提示。
data_type：可选子句 AS data_type 改变序列的数据类型。有效类型是 smallint、integer 和 bigint。当且仅当先前的最小值和最大值是旧数据类型的最小值或最大值时（换句话说，如果序列是使用 NO MINVALUE 或 NO MAXVALUE，隐式或显式创建的），则更改数据类型会自动更改序列的最小值和最大值。否则，将保留最小值和最大值，除非将新值作为同一命令的一部分给出。如果最小值和最大值不符合新的数据类型，则会生成错误。
increment：子句 INCREMENT BY increment 是可选的。一个正值将产生一个上升序列，一个负值会产生一个下降序列。如果没有指定，旧的增量值将被保持。
minvalue|NO MINVALUE：可选的子句 MINVALUE minvalue 决定一个序列能产生的最小值。如果指定了 NO MINVALUE，上升序列和下降序列的默认值分别是 1 和 数据类型的最小值。如果这些选项都没有被指定，将保持当前的最小值。
maxvalue|NO MAXVALUE：可选的子句 MAXVALUE maxvalue 决定一个序列能产生的最大值。如果指定了 NO MAXVALUE，上升序列和下降序列的默认值分别是数据类型的最大值和 -1。如果这些选项都没有被指定，将保持当前的最大值。
start：可选的子句 START WITH start 更改该序列被记录的开始值。这对于当前序列值没有影响，它会简单地设置未来 ALTER SEQUENCE RESTART 命令将会使用的值。
restart：可选的子句 RESTART [ WITH restart ] 更改该序列的当前值。这类似于用 is_called = false 调用setval函数：被指定的值将会被 下一次 nextval 调用返回。写上没有 restart 值的 RESTART 等效于提供被 CREATE SEQUENCE 记录的或者上一次被 ALTER SEQUENCE START WITH 设置的开始值。与 setval 调用相比，序列上的 RESTART 操作是事务性的并阻止并发事务从同一序列中获取数字。如果这不是所需的操作模式，则应使用 setval。
cache：子句 CACHE cache 使得序列数字被预先分配并且保存在内存中以便更快的访问。最小值是 1（每次只产生一个值，即无缓存）。如果没有指定，旧的缓冲值将被保持。
CYCLE：可选的 CYCLE 关键词可以被用来允许该序列在达到 maxvalue（上升序列）或 minvalue（下降序列）时回卷。如果到达该限制，下一个被产生的数字将分别是 minvalue 或者 maxvalue。
NO CYCLE：如果指定了可选的 NO CYCLE 关键词，任何在该序列到达其最大值后的 nextval 调用将会返回一个错误。如果既没有指定 CYCLE 也没有指定 NO CYCLE，旧的循环行为将被保持。
OWNED BY table_name.column_name|OWNED BY NONE：OWNED BY 选项导致该序列与一个特定的表列相关联，这样如果该列（或者整个表）被删除，该序列也会被自动删除。如果指定，这种关联会替代之前为该序列指定的任何关联。被指定的表必须具有相同的 拥有者并且与该序列在同一个模式中。指定 OWNED BY NONE 可以移除任何现有的关联，让该序列 “自立”。
new_owner：该序列的新拥有者的用户名。
new_name：该序列的新名称。
new_schema：该序列的新模式。

3.2 示例

--修改序列隶属于表，删除表时索引也关联删除
postgres=# alter sequence seq_t owned by t.id;
ALTER SEQUENCE
postgres=# \d seq_tSequence "public.seq_t"Type  | Start | Minimum |       Maximum       | Increment | Cycles? | Cache
--------+-------+---------+---------------------+-----------+---------+-------bigint |     1 |       1 | 9223372036854775807 |         1 | no      |     1
Owned by: public.t.idpostgres=# drop table t;
DROP TABLE
postgres=# \d seq_t
Did not find any relation named "seq_t".

四、删除序列

4.1 语法

DROP SEQUENCE [ IF EXISTS ] name [, ...] [ CASCADE | RESTRICT ]

参数说明：

IF EXISTS：如果该序列不存在则不要抛出一个错误，而是发出一个提示。
name：一个序列的名称（可以是模式限定的）。
CASCADE：自动删除依赖于该序列的对象，然后删除所有依赖于那些对象的对象。
RESTRICT：如果有任何对象依赖于该序列，则拒绝删除它。这是默认值。

4.2 示例

postgres=# drop sequence seq_t;
DROP SEQUENCE