如何将JSON传入Postgres的COPY tmp FROM PROGRAM命令？

你遇到的核心问题是JSON字符串中的双引号在shell命令中被意外解析，导致COPY读取到不完整的JSON内容，同时单引号的转义方式不正确，无法在shell命令中正确传递完整的JSON数据。

错误根源 #

当你用format的%s插入json_in时，生成的shell命令会变成：

echo '{"key1": "val1", "key2": "val2"}' | jq .

shell会把JSON中的双引号当作命令分隔符，实际echo输出的内容被截断成{，导致COPY读取到不完整的JSON，触发语法错误。

解决方案 #

使用format函数的%L占位符，它会自动将输入字符串转义为带单引号包裹且内部特殊字符已转义的格式，确保shell能完整识别JSON字符串。

修正后的函数代码 #

CREATE OR REPLACE FUNCTION json_func(IN json_in JSONB, OUT json_out JSONB)
LANGUAGE plpgsql
AS $code_block$
BEGIN
CREATE TEMPORARY TABLE tmp (json_tmp JSONB);
-- 使用%L替代%s，自动处理字符串转义
EXECUTE format($bash$ COPY tmp FROM PROGRAM 'echo %L | jq . ' $bash$, json_in);
SELECT json_tmp FROM tmp INTO json_out;
DROP TABLE tmp;
END;
$code_block$;

测试验证 #

执行原查询语句：

SELECT json_func('{"key1": "val1", "key2": "val2"}'::JSONB);

此时生成的shell命令会被正确转义，确保echo输出完整的JSON内容，jq和COPY都能正常处理数据，不会再出现截断错误。

补充说明 #

如果你的场景需要更复杂的shell命令转义，Postgres 14及以上版本支持quote_shell函数，可以专门处理shell层面的字符串转义，但%L在当前场景下已经足够解决问题。

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