PostgreSQL使用row_number()时INSERT数据顺序未保留的问题诉求

问题根源 #

核心问题在于：row_number() 未指定明确排序规则时，PostgreSQL无法保证分区内的行顺序；同时数据库表本身是无序集合，插入后数据存储顺序不固定，仅靠查询时的“巧合顺序”完全不可靠。要让seq完全匹配cd_dtl_exp数组unnest后的元素顺序，必须绑定数组的原始位置信息。

解决方案 #

方案1：直接用unnest_with_ordinality生成seq

PostgreSQL提供unnest_with_ordinality函数，展开数组时会同时返回元素在原数组中的位置序号（从1开始），用这个序号作为seq最直接可靠：

INSERT INTO temp_table (item_cd, header, seq, cd_dtl)
SELECT 
    ot.item_cd, 
    ot.header, 
    u.ordinal AS seq, 
    u.cd_dtl
FROM original_table ot
CROSS JOIN UNNEST(ot.cd_dtl_exp) WITH ORDINALITY u(cd_dtl, ordinal);

• WITH ORDINALITY会给每个展开元素分配原始位置序号，完全匹配数组元素的先后顺序
• 无需额外使用row_number()，避免因排序规则缺失导致的顺序混乱

方案2：保留row_number()但指定排序依据

如果必须用row_number()生成seq，一定要在OVER子句的分区后加上ORDER BY，绑定unnest的原始位置：

INSERT INTO temp_table (item_cd, header, seq, cd_dtl)
SELECT 
    ot.item_cd, 
    ot.header, 
    row_number() OVER (PARTITION BY ot.item_cd ORDER BY u.ordinal) AS seq, 
    u.cd_dtl
FROM original_table ot
CROSS JOIN UNNEST(ot.cd_dtl_exp) WITH ORDINALITY u(cd_dtl, ordinal);

• 用u.ordinal作为排序字段，强制row_number()按照数组元素的原始顺序生成序号

关键提醒 #

数据库表本身是无序的，即使插入时顺序正确，查询temp_table时如果不指定ORDER BY，结果顺序依然可能随机。因此查询时必须加上排序条件：

SELECT * FROM temp_table ORDER BY item_cd, seq;

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