PostgreSQL存储过程批量插入JSON数组报错，求正确实现方案

错误原因 #

你遇到的"column "id" does not exist"错误，根本原因是JSON键的访问语法错误，和serial类型无关。代码中_elem->id的写法会让PostgreSQL误以为id是表的列名，而非JSON对象的键。正确访问JSON键需要用单引号包裹，比如_elem->'id'；而且->返回的是JSON类型，不能直接作为format的参数，得先转成对应数据类型（比如(_elem->'id')::int或_elem->>'id'）。

另外，你手动拼接JSON的做法完全多此一举，不仅容易出错，还浪费性能——json_populate_record可以直接处理原始的JSON对象，无需重新构造。

正确的插入方式 #

根据你是否需要从JSON中指定id（serial类型是自增主键，通常不需要手动传入），分两种场景：

场景1：JSON包含id值，需手动插入 #

直接用json_populate_record将JSON对象映射为product表的行结构，无需手动拼接：

CREATE OR REPLACE PROCEDURE insert_into_product(entries json[]) 
LANGUAGE plpgsql AS $$
DECLARE 
    _elem json;
BEGIN
    FOREACH _elem IN ARRAY entries
    LOOP 
        -- 直接提取json_populate_record返回的行数据插入
        INSERT INTO product 
        SELECT (json_populate_record(NULL::product, _elem)).*;
    END LOOP;
END;
$$;

场景2：id使用serial自增（推荐） #

如果JSON中没有id，或希望数据库自动生成主键，只需忽略id字段：

CREATE OR REPLACE PROCEDURE insert_into_product(entries json[]) 
LANGUAGE plpgsql AS $$
DECLARE 
    _elem json;
BEGIN
    FOREACH _elem IN ARRAY entries
    LOOP 
        INSERT INTO product (name, price)
        SELECT (json_populate_record(NULL::product, _elem)).name,
               (json_populate_record(NULL::product, _elem)).price;
    END LOOP;
END;
$$;

更高效的批量插入写法 #

可以不用循环，用unnest展开JSON数组，一次性完成插入，性能更优：

CREATE OR REPLACE PROCEDURE insert_into_product(entries json[]) 
LANGUAGE plpgsql AS $$
BEGIN
    INSERT INTO product (name, price)
    SELECT (json_populate_record(NULL::product, elem)).name,
           (json_populate_record(NULL::product, elem)).price
    FROM unnest(entries) AS elem;
END;
$$;

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