PostgreSQL 教程: 序列

九月 10, 2023

摘要:在本教程中,您将了解 PostgreSQL 序列以及如何使用序列对象生成数字序列。

目录

根据定义,序列是整数的有序列表。序列中数字的顺序很重要。例如,{1,2,3,4,5}{5,4,3,2,1}是完全不同的序列。

PostgreSQL 中的序列是用户定义的模式绑定对象,它根据指定的规范生成整数序列。

要在 PostgreSQL 中创建序列,请使用CREATE SEQUENCE语句。

PostgreSQL CREATE SEQUENCE 语句简介

下面说明了CREATE SEQUENCE语句的语法:

CREATE SEQUENCE [ IF NOT EXISTS ] sequence_name
    [ AS { SMALLINT | INT | BIGINT } ]
    [ 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 } ]

sequence_name

CREATE SEQUENCE子句后指定序列名称。指定IF NOT EXISTS关键词时,仅当新序列不存在时才有条件地创建新序列。

序列名称必须与同一模式中的任何其他序列、表、索引视图或外部表不同。

[ AS { SMALLINT | INT | BIGINT } ]

指定序列的数据类型。有效数据类型为SMALLINTINTBIGINT。如果您跳过它,则默认数据类型为BIGINT

序列的数据类型决定了序列的最小值和最大值。

[ INCREMENT [ BY ] increment ]

increment指定将哪个值添加到当前序列值以创建新值。

正数将形成升序,负数将形成降序。

默认增量值为 1。

[ MINVALUE minvalue | NO MINVALUE ]

[ MAXVALUE maxvalue | NO MAXVALUE ]

定义序列的最小值和最大值。如果使用NO MINVALUENO MAXVALUE,序列将使用默认值。

对于升序序列,默认最大值为序列数据类型的最大值,默认最小值为 1。

如果是降序序列,则默认最大值为 -1,默认最小值为序列数据类型的最小值。

[ START [ WITH ] start ]

START子句指定序列的起始值。

默认的起始值,对应于升序序列的minvalue,降序序列的maxvalue

cache

CACHE确定有多少序列号被预先分配,并存储在内存中以便更快地访问。一次可以生成一个值。

默认情况下,序列一次生成一个值,即没有缓存。

CYCLE | NO CYCLE

如果达到限制,CYCLE允许重新启动序列值。下一个数字将是升序的最小值和降序的最大值。

如果使用NO CYCLE,当达到限制时,尝试获取下一个值将导致错误。

如果您没有明确指定CYCLENO CYCLE,则默认是NO CYCLE

OWNED BY table_name.column_name

OWNED BY子句允许您将表列与序列关联起来,这样当您删除列或表时,PostgreSQL 将自动删除关联的序列。

请注意,当您对表的列使用伪类型SERIAL时,PostgreSQL 会在幕后自动创建与该列关联的序列。

PostgreSQL CREATE SEQUENCE 示例

让我们看一些创建序列的例子来更好地理解。

1) 创建升序序列示例

该语句使用CREATE SEQUENCE语句创建一个从 100 开始、增量为 5 的新升序序列:

CREATE SEQUENCE mysequence
INCREMENT 5
START 100;

要从序列中获取下一个值,请使用nextval()函数:

SELECT nextval('mysequence');

PostgreSQL Sequence - simple example

如果再次执行该语句,您将从序列中获取下一个值:

SELECT nextval('mysequence');

PostgreSQL Sequence - nextval example

2) 创建降序序列示例

以下语句使用CYCLE选项创建从 3 到 1 的降序序列:

CREATE SEQUENCE three
INCREMENT -1
MINVALUE 1 
MAXVALUE 3
START 3
CYCLE;

当您多次执行以下语句时,您将看到数字从 3、2、1 开始,回到 3、2、1,依此类推:

SELECT nextval('three');

3) 创建与表列关联的序列

首先,创建一个新表,名为order_details

CREATE TABLE order_details(
    order_id SERIAL,
    item_id INT NOT NULL,
    item_text VARCHAR NOT NULL,
    price DEC(10,2) NOT NULL,
    PRIMARY KEY(order_id, item_id)
);

其次,创建与order_details表的item_id列关联的新序列:

CREATE SEQUENCE order_item_id
START 10
INCREMENT 10
MINVALUE 10
OWNED BY order_details.item_id;

第三步,将三个订单行项目插入order_details表中:

INSERT INTO 
    order_details(order_id, item_id, item_text, price)
VALUES
    (100, nextval('order_item_id'),'DVD Player',100),
    (100, nextval('order_item_id'),'Android TV',550),
    (100, nextval('order_item_id'),'Speaker',250);

在此语句中,我们使用nextval()函数从order_item_id序列中获取项目 id 值。

第四步,从order_details表中查询数据:

SELECT
    order_id,
    item_id,
    item_text,
    price
FROM
    order_details;

PostgreSQL Sequence in a table

列出数据库中的所有序列

要列出当前数据库中的所有序列,请使用以下查询:

SELECT
    relname sequence_name
FROM 
    pg_class 
WHERE 
    relkind = 'S';

删除序列

如果序列与表列关联,则一旦删除表列或删除表,序列就会自动删除。

您还可以使用DROP SEQUENCE语句手动删除序列:

DROP SEQUENCE [ IF EXISTS ] sequence_name [, ...] 
[ CASCADE | RESTRICT ];

在这个语法中:

  • 首先,指定要删除的序列的名称。IF EXISTS选项有条件地删除序列(如果存在)。如果您想一次删除多个序列,可以使用逗号分隔的序列名称列表。
  • 然后,如果要递归删除依赖于序列的对象以及依赖于从属对象的对象等,请使用CASCADE选项。

PostgreSQL DROP SEQUENCE 语句示例

该语句删除order_details表。由于order_item_id序列与order_details表的item_id列相关联,因此它也会自动删除:

DROP TABLE order_details;

在本教程中,您了解了 PostgreSQL 序列以及如何使用序列对象生成序列列表。