PostgreSQL中优化JSON批量插入并返回插入ID的精简实现

现有language表结构如下：

lan_id INT_2 IDENTITY,
lan_code TEXT

需要创建一个接收JSON字符串的数据库函数，将数据插入language表并返回插入的ID，JSON中的sort_position字段可唯一标识每条记录。示例JSON如下：

'[
  {"id": null, "name": "Farsi", "sort_position": 5},
  {"id": null, "name": "Thai", "sort_position": 8}
]'

目前已有实现代码，但结构冗余，希望找到更紧凑的写法（比如无需拆分多个独立语句完成插入和临时表更新），现有代码如下：

CREATE TEMP TABLE language_to_insert AS
SELECT *
FROM json_populate_recordset(null::record, '[{"id": null, "name": "Farsi", "sort_position": 5}, {"id": null, "name": "Thai", "sort_position": 8}]') AS record_data
(
    id INT2,
    name TEXT,
    sort_position INT4
);

CREATE TEMP TABLE tmp_language (lan_id INT2);
WITH language_inserted AS (
    INSERT INTO language(lan_name)
    SELECT language_to_insert.name
    FROM language_to_insert
    WHERE language_to_insert.id IS NULL
    RETURNING language.lan_id
)
INSERT INTO tmp_language
SELECT language_inserted.lan_id FROM language_inserted;

-- 更新JSON输入对应的id为插入后的lan_id
WITH match_rows AS (
    SELECT t1.sort_position, t2.lan_id
    FROM (SELECT language_to_insert.sort_position, row_number() over() rn1 FROM language_to_insert WHERE language_to_insert.id IS NULL) t1
    JOIN (SELECT tmp_language.lan_id, row_number() over() rn2 FROM tmp_language) t2
    ON rn1 = rn2
)
UPDATE language_to_insert
SET id = match_rows.lan_id
FROM match_rows
WHERE language_to_insert.sort_position=match_rows.sort_position;

select * from language_to_insert;

可以通过CTE（公共表表达式）整合逻辑，省去中间临时表，同时利用sort_position唯一的特性直接关联数据，无需通过行号匹配，以下是两种优化方案：

方案一：保留单临时表，合并插入与更新 #

-- 解析JSON到临时表
CREATE TEMP TABLE language_to_insert AS
SELECT *
FROM json_populate_recordset(null::record, '[{"id": null, "name": "Farsi", "sort_position": 5}, {"id": null, "name": "Thai", "sort_position": 8}]') AS record_data
(
    id INT2,
    name TEXT,
    sort_position INT4
);

-- 插入数据的同时直接更新临时表的id字段，一步完成
WITH inserted AS (
    INSERT INTO language(lan_name)
    SELECT name
    FROM language_to_insert
    WHERE id IS NULL
    -- 关联原临时表，返回插入的lan_id和对应的sort_position
    RETURNING lan_id, (SELECT sort_position FROM language_to_insert WHERE name = language.lan_name) AS sort_position
)
UPDATE language_to_insert
SET id = inserted.lan_id
FROM inserted
WHERE language_to_insert.sort_position = inserted.sort_position;

-- 返回最终结果
SELECT * FROM language_to_insert;

方案二：完全去掉临时表，用CTE串联所有逻辑 #

如果不需要保留临时表，可以直接用CTE完成JSON解析、数据插入和结果返回：

WITH parsed_data AS (
    -- 解析输入的JSON数据
    SELECT *
    FROM json_populate_recordset(null::record, '[{"id": null, "name": "Farsi", "sort_position": 5}, {"id": null, "name": "Thai", "sort_position": 8}]') AS record_data
    (
        id INT2,
        name TEXT,
        sort_position INT4
    )
), inserted_data AS (
    -- 插入数据并返回lan_id和对应的sort_position
    INSERT INTO language(lan_name)
    SELECT pd.name
    FROM parsed_data pd
    WHERE pd.id IS NULL
    RETURNING lan_id, pd.sort_position
    FROM parsed_data pd
    WHERE pd.name = language.lan_name
)
-- 组合原数据与插入后的ID，返回结果
SELECT 
    COALESCE(id_data.lan_id, pd.id) AS id,
    pd.name,
    pd.sort_position
FROM parsed_data pd
LEFT JOIN inserted_data id_data ON pd.sort_position = id_data.sort_position;

注意事项 #

如果name字段存在重复值，方案一中的子查询可能出现异常，此时建议使用方案二的关联方式，确保lan_id和sort_position的对应关系准确。

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