postgresql - 如何将 CSV 复制为 JSON 字段

Question

有没有办法将 CSV 文件数据直接复制到 JSON 或 JSONb 数组中？

例子:

CREATE TABLE mytable (
    id serial PRIMARY KEY,
    info jSONb -- or JSON
);
COPY mytable(info) FROM '/tmp/myfile.csv' HEADER csv;

注意:每个 CSV 行都映射到一个 JSON 数组。这是一个普通的 CSV。

---

普通 CSV(未嵌入 JSON)... /tmp/myfile.csv =

a,b,c
100,Mum,Dad
200,Hello,Bye

正确的 COPY 命令必须等同于通常的复制波纹管。

普通 COPY(丑陋但工作正常)

CREATE TEMPORARY TABLE temp1 (
  a int, b text, c text
);
COPY temp1(a,b,c) FROM '/tmp/myfile.csv' HEADER csv;

INSERT INTO mytable(info) SELECT json_build_array(a,b,c) FROM temp1;

它很丑因为:

• 需要关于字段的先验知识，以及之前的CREATE TABLE。

• “大数据”需要一个大的临时表，这样就浪费了 CPU、磁盘和我的时间 — 表 mytable 的每一行都有 CHECKs 和 UNIQUEs 约束。

• ...需要多于 1 个 SQL 命令。

Answer 1

完美解决!

不需要知道所有 CSV 列，只提取您知道的内容。

在 SQL CREATE EXTENSION PLpythonU; 中使用:如果命令产生类似“无法打开扩展控制文件......没有这样的文件”的错误，你需要安装pg-py 额外包。在标准 UBUNTU (16 LTS) 中很简单，apt install postgresql-contrib postgresql-plpython。

CREATE FUNCTION get_csvfile(
  file text,
  delim_char char(1) = ',',
  quote_char char(1) = '"')
returns setof text[] stable language plpythonu as $$
  import csv
  return csv.reader(
     open(file, 'rb'),
     quotechar=quote_char,
     delimiter=delim_char,
     skipinitialspace=True,
     escapechar='\\'
  )
$$;

INSERT INTO mytable(info)
  SELECT jsonb_build_array(c[1],c[2],c[3]) 
  FROM get_csvfile('/tmp/myfile1.csv') c;

split_csv() 函数是 defined here . csv.reader 非常可靠 (!)。

未针对 big-big CSV 进行测试...但预期 Python 可以完成工作。

---

PostgreSQL 解决方法

这不是一个完美的解决方案，但它解决了主要问题，即

... big temporary table, so lost CPU, disk and my time"...

这就是我们的方法，使用 file_fdw 的解决方法!

1. 采用您的约定以避免文件复制和文件权限混淆... CSV 的标准文件路径。示例:/tmp/pg_myPrj_file.csv

2. 使用魔术扩展初始化您的数据库或 SQL 脚本，

   CREATE EXTENSION file_fdw;
   CREATE SERVER files FOREIGN DATA WRAPPER file_fdw;

1. 对于每个 CSV 文件，myNewData.csv，

   3.1。为您的新文件创建符号链接(symbolic link)(或 scp 远程副本)ln -sf $PWD/myNewData.csv/tmp/pg_socKer_file.csv

   3.2。为您的新表配置 file_fdw(假设 mytable)。

   CREATE FOREIGN TABLE temp1 (a int, b text, c text) 
   SERVER files OPTIONS ( 
     filename '/tmp/pg_socKer_file.csv', 
       format 'csv', 
       header 'true'
   );

PS:在使用psql 运行SQL 脚本后，当遇到一些权限问题时，通过sudo chown -h postgres:postgres/tmp/pg_socKer_file.csv 更改链接的所有者>。

3.3。使用 file_fdw 表作为源(假设填充 mytable)。

 INSERT INTO mytable(info)
 SELECT json_build_array(a,b,c) FROM temp1;

---

感谢@JosMac(和 his tutorial)!

---

注意:如果有 STDIN 方法(存在？？)，会很容易，避免权限问题和使用绝对路径。参见 this answer/discussion .