postgresql中的sequence

编辑
Database

sequence 号是数据库系统按照一定规则自增的数字序列 sequence 操作函数

Function Return Type Description
currval(regclass) bigint Return value most recently obtained with nextval for specified sequence
lastval() bigint Return value most recently obtained with nextval for any sequence
nextval(regclass) bigint Advance sequence and return new value
setval(regclass, bigint) bigint Set sequence's current value
setval(regclass, bigint, boolean) bigint Set sequence's current value and is_called flag

创建 sequence

postgre文档

sequence 操作函数定义

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 } ]

<!-- 创建一个从101开始的增序列 -->
CREATE SEQUENCE serial START 101;

<!-- 选择下一个 -->
SELECT nextval（'serial'）;

设置自增列

sequence 是 postgre10 以前对自增列对实现方式， 数据库表添加一个自增类型我们可以设置类型添加为 bigserial，bigserial 是一个伪类型，我们直接用修改字段是不可以的，一般我们需要

  • 创建一个 sequence：CREATE SEQUENCE ${sequenceName};
  • 设置该字段的默认值为该 sequence 的下一个：ALTER TABLE ${table} ALTER COLUMN ${field} SET DEFAULT nextval('${sequenceName}');
  • 将该 sequence 与该表相关联，这样如果该表删除 sequence 也会被删除：ALTER SEQUENCE ${sequenceName} OWNED BY ${table}.${field};
  • 设置该 sequence 的起始值：SELECT setval('${sequenceName}', COALESCE(max(${field}), 0)) FROM ${table};

postgre10 后引入了 identity 标识符设置自增列, create table 语法中，在列的类型后使用如下语法定义 identity 列。 ALWAYS，表示优先使用系统列生成的自增值,如果要覆盖系统产生的值，需要使用 OVERRIDING SYSTEM VALUE,否则会报错。 BY DEFAULT，表示优先使用用户输入的值，可以与自增值重复。

-- 创建一个含有IDENTITY的表
CREATE TABLE public.test
(
    _id bigint GENERATED BY DEFAULT AS IDENTITY
)

-- To add an IDENTITY column to  table staff
ALTER TABLE staff ADD COLUMN staff_id int GENERATED BY DEFAULT AS IDENTITY;

-- 删除test表中_id的IDENTITY
ALTER TABLE test ALTER COLUMN _id DROP IDENTITY;
<!-- 不能用删除默认值的方法删除IDENTITY -->
ALTER TABLE test ALTER COLUMN _id DROP DEFAULT;

-- 修改test表中_id的为IDENTITY
ALTER TABLE testta b le
    ALTER COLUMN _id ADD GENERATED BY DEFAULT AS IDENTITY;