postgresql中的sequence

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;