Question: How to COPY CSV as JSON fields

Question

How to COPY CSV as JSON fields

Answers 1
Added at 2017-01-04 15:01
Tags
Question

Is there a way to COPY the CSV file data directly into a JSON or JSONb array?

Example:

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

NOTE: each CSV line is mapped to a JSON array. It is a normal CSV.


Normal CSV (no JSON-embeded)... /tmp/myfile.csv =

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

The correct COPY command must be equivalent to the usual copy bellow.

Usual COPY (ugly but works fine)

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;

It is ugly because:

  • need the a priory knowledge about fields, and a previous CREATE TABLE with it.

  • for "big data" need a big temporary table, so lost CPU, disk and my time — the table mytable have CHECKs and UNIQUEs constraints for each line.

  • ... Needs more than 1 SQL command.

Answers to

How to COPY CSV as JSON fields

nr: #1 dodano: 2017-01-04 22:01

It is not a perfect solution, but solve the main problem, that is the

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

This is the way we do it, a workaround with file_fdw!

  1. Adopt your conventions to avoid file-copy and file-permission confusions... The standard file path for a CSV. Example: /tmp/pg_myPrj_file.csv

  2. Initialize your database or SQL script with the magic extension,

   CREATE EXTENSION file_fdw;
   CREATE SERVER files FOREIGN DATA WRAPPER file_fdw;
  1. For each CSV file, myNewData.csv,

    3.1. make a symbolic link (or scp remote copy) for your new file ln -sf $PWD/myNewData.csv /tmp/pg_socKer_file.csv

    3.2. configure the file_fdw for your new table (suppose 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: after run SQL script with psql, when having some permission problem, change owner of the link by sudo chown -h postgres:postgres /tmp/pg_socKer_file.csv.

3.3. use the file_fdw table as source (suppose populating mytable).

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

Thanks to @JosMac (and his tutorial)!


NOTE: if there are a STDIN way to do it (exists??), will be easy, avoiding permission problems and use of absolute paths. See this answer/discussion.

Source Show
◀ Wstecz