PostgreSQL CREATE SEQUENCE用法及代码示例

PostgreSQL 中的序列是用户定义的 schema-bound 对象，它根据指定的规范生成整数序列。 CREATE SEQUENCE 语句用于在 PostgreSQL 中创建序列。

用法：
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 } ]

现在让我们分析一下上面的语法：

• 首先，在 CREATE SEQUENCE 子句之后设置序列的名称。 IF NOT EXISTS 仅在新序列不存在时有条件地创建它。
• 其次，指定序列的数据类型。有效数据类型为 SMALLINT、INT 和 BIGINT。如果跳过它，默认数据类型为 BIGINT。它用于确定序列的最小值和最大值。
• 增量指定将哪个值添加到当前序列值以创建新值。正数构成升序，负数构成降序。默认增量值为 1。
• 然后，我们定义序列的最小值和最大值。如果您使用 NO MINVALUE 或 NO MAXVALUE，则序列将使用默认值。对于升序序列，默认最大值为序列数据类型的最大值，默认最小值为 1；对于降序序列，默认最大值为 -1，默认最小值为最小值序列的数据类型的值。
• START 子句指定序列的起始值。
• 如果达到限制，CYCLE 允许您重新启动该值。下一个数字将是升序的最小值和降序的最大值。如果使用 NO CYCLE，当达到限制时，尝试获取下一个值将导致错误。如果您没有明确指定 CYCLE 或 NO CYCLE，则 NO CYCLE 是默认值。
• OWNED BY 子句允许您将表列与序列相关联，这样当您删除列或表时，PostgreSQL 将自动删除关联的序列。

现在让我们进入一些例子。

范例1：
在这个例子中，我们将使用 CREATE SEQUENCE 语句创建一个新的升序序列，从 10 开始，增量为 5：

CREATE SEQUENCE mysequence
INCREMENT 5
START 10;

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

SELECT nextval('mysequence');

它将导致以下输出：

现在，如果我们重复 nexval() 函数，我们会得到递增的值。

输出：

范例2：
以下语句使用循环选项创建从 3 到 1 的降序序列：

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

当您多次执行以下语句时，您将看到数字从 3、2、1 开始并返回到 3、2、1 等：

SELECT nextval('three');

输出：

范例3：
在本例中，我们将使用以下语句创建与表列关联的序列：

CREATE TABLE order_details(
    order_id SERIAL,
    item_id INT NOT NULL,
    product_id INT,
    product_name TEXT NOT NULL,
    price DEC(10, 2) NOT NULL,
    PRIMARY KEY(order_id, item_id)
);

其次，创建一个与关联的新序列item_id 的列order_details 表：

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, product_name, 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);

四、从order_details表查询数据：

SELECT
    order_id,
    item_id,
    product_name,
    price
FROM
    order_details;

输出：