PostgreSQL：使用nextval生成ID乱序，求有序生成ID的方法

我在PostgreSQL中创建了如下表：

CREATE TABLE IF NOT EXISTS "table"
(
    ID bigserial NOT NULL,
    Fruit varchar(256) NOT NULL,
    CONSTRAINT id_pk PRIMARY KEY (ID)
);

（注：原语句存在两处笔误：CREAT应为CREATE，字段与约束之间缺少逗号）

随后我尝试用nextval结合pg_get_serial_sequence为每条Fruit记录生成ID，执行的SQL语句如下：

select 
    nextval(pg_get_serial_sequence('table', 'ID')) AS ID, -- 原语句多了多余的END关键字
    fruit
from
    fruit_source_table

我期望得到的结果（Fruit列顺序必须为Apple、Cherry、Banana）：

但实际得到的结果是：

问题：如何让ID按1到3的顺序生成，同时保持Fruit列顺序与源表一致？

问题根源 #

PostgreSQL执行无ORDER BY的查询时，会根据底层存储和查询优化策略选择数据扫描顺序，nextval会在每行被处理时调用，最终输出的行顺序可能和nextval的调用顺序不匹配，导致ID与行的对应关系混乱。另外，没有显式ORDER BY的查询结果顺序是不被PostgreSQL保证的，哪怕源表看起来是有序的。

可行解决方法 #

方法1：插入时指定顺序，依赖serial自动生成ID

这是最直接的方式：先按要求的顺序从源表取数据，插入目标表后，bigserial类型的ID会自动按插入顺序生成连续值：

INSERT INTO "table" (Fruit)
SELECT Fruit
FROM fruit_source_table
-- 显式指定排序规则，确保Fruit按Apple→Cherry→Banana的顺序返回
ORDER BY CASE Fruit 
    WHEN 'Apple' THEN 1 
    WHEN 'Cherry' THEN 2 
    WHEN 'Banana' THEN 3 
END;

插入完成后查询目标表，就能得到ID按1-3连续递增、Fruit顺序符合要求的结果。

方法2：用row_number()生成有序ID后插入

如果需要在查询阶段就生成有序ID，再插入目标表，可以用row_number()函数：

-- 先查询得到带有序ID的结果
SELECT
    row_number() OVER (ORDER BY CASE Fruit 
        WHEN 'Apple' THEN 1 
        WHEN 'Cherry' THEN 2 
        WHEN 'Banana' THEN 3 
    END) AS ID,
    Fruit
FROM fruit_source_table;

如果要将这些数据插入目标表，需先重置序列避免ID冲突：

-- 重置序列到目标表当前最大ID+1（如果表已有数据）
SELECT setval(pg_get_serial_sequence('table', 'ID'), (SELECT COALESCE(MAX(ID), 0) FROM "table") + 1);

-- 插入带有序ID的数据
INSERT INTO "table" (ID, Fruit)
SELECT
    row_number() OVER (ORDER BY CASE Fruit 
        WHEN 'Apple' THEN 1 
        WHEN 'Cherry' THEN 2 
        WHEN 'Banana' THEN 3 
    END) + (SELECT COALESCE(MAX(ID), 0) FROM "table") AS ID,
    Fruit
FROM fruit_source_table;

关键提示 #

• 永远不要依赖无ORDER BY的查询结果顺序，这是关系型数据库的基本规则。
• 如果有并发插入操作，bigserial的序列可能会出现ID不连续的情况，但一次性批量插入有序数据时，方法1可以保证ID按插入顺序连续递增。

内容的提问来源于stack exchange，提问作者CCM