PostgreSQL使用COPY命令时去除JSON输入字符串的换行符

我有一段将JSON文件导入临时表的PostgreSQL代码：

DROP TABLE IF EXISTS tmp;
CREATE TEMP table tmp (c JSONB );

sqltxt:='COPY tmp from '''||filepath||''' with (FORMAT TEXT, DELIMITER ''~'')';

目前必须手动去除传入文件中的换行符才能成功处理，我想通过regexp_replace函数在代码里自动完成这个操作，但尝试后遇到了问题。

我的尝试代码：

DROP TABLE IF EXISTS tmp;
CREATE TEMP table tmp (c JSONB );

-- Populate temp table with incoming JSON
sqltxt:='COPY translate(tmp, E''\n,'', '''') from '''||filepath||''' with (FORMAT TEXT, DELIMITER ''~'')';
EXECUTE sqltxt;

执行后报错：

COPY translate(tmp, E'\n,', '') from 'C:\ChrisDev\Readings\14.json' with (FORMAT TEXT, DELIMITER '~')

[42601] ERROR: syntax error at or near "E'\n,'"

错误原因 #

你错误地把translate函数直接放在了COPY语句的表名位置，COPY语法不支持这种直接对表应用函数的写法。要处理文件内容，得换一种思路——要么先导入再处理数据，要么在导入前用外部命令预处理文件，或者直接读取文件内容后处理插入。

解决方案 #

方案一：导入后批量处理数据 #

这种方式最直观，先正常导入数据，再用regexp_replace去除换行符：

DROP TABLE IF EXISTS tmp;
CREATE TEMP TABLE tmp (c JSONB);

-- 先执行原导入逻辑
sqltxt := 'COPY tmp FROM ''' || filepath || ''' WITH (FORMAT TEXT, DELIMITER ''~'')';
EXECUTE sqltxt;

-- 去除JSON中的所有换行符，重新转换为JSONB类型
UPDATE tmp 
SET c = regexp_replace(c::TEXT, '\n', '', 'g')::JSONB;

方案二：导入时用PROGRAM预处理文件 #

如果文件体积较大，不想先导入再更新，可以用COPY的PROGRAM选项调用系统命令，在导入前就把换行符去掉：

Windows环境（用PowerShell）

DROP TABLE IF EXISTS tmp;
CREATE TEMP TABLE tmp (c JSONB);

sqltxt := 'COPY tmp FROM PROGRAM ''powershell -Command "(Get-Content ''''' || filepath || '''''') -replace ''\n'', ''''"'' WITH (FORMAT TEXT, DELIMITER ''~'')';
EXECUTE sqltxt;

注意：这里的引号嵌套需要用四个单引号表示一个实际的单引号，避免语法冲突

Linux/macOS环境（用tr命令）

DROP TABLE IF EXISTS tmp;
CREATE TEMP TABLE tmp (c JSONB);

sqltxt := 'COPY tmp FROM PROGRAM ''tr -d "\n" < ''''' || filepath || ''''' WITH (FORMAT TEXT, DELIMITER ''~'')';
EXECUTE sqltxt;

方案三：用pg_read_file直接读取并处理文件 #

如果文件在PostgreSQL的数据目录下，或者你拥有超级用户权限，可以直接读取文件内容处理后插入：

DROP TABLE IF EXISTS tmp;
CREATE TEMP TABLE tmp (c JSONB);

INSERT INTO tmp (c)
SELECT regexp_replace(pg_read_file(filepath), '\n', '', 'g')::JSONB;

注意：这个方案有局限性，pg_read_file默认只能读取数据库数据目录内的文件，跨路径读取需要超级用户权限

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