I have a CSV file which each line is a JSON and to be copied into a temp table.
Temp table is created by:
CREATE TEMP TABLE temp_table(json_rec jsonb) ON COMMIT DROP;
Each records in this table will be copied into another temp table but this is another topic.
Lines look like:
{"id":300762,"name":"Ik wil buiten","short_name":"Ik wil buiten","exceptions":[{"type_id":3},{"type_id":3},{"type_id":4}]}
{"id":300762,"name":"Ik \"wil\" buiten","short_name":"Ik wil buiten","exceptions":[{"type_id":3},{"type_id":3},{"type_id":4}]}
These lines can have escaped double quotes as the second line.
To copy these lines into the temp table I invoke the method of postgres library:
public long copyIn(final String sql, InputStream from) throws SQLException, IOException {
return copyIn(sql, from, DEFAULT_BUFFER_SIZE);
}
with query
COPY temp_table FROM STDIN
and csv file as inputstream
It throws PSQLException:
invalid input syntax for type json Detail: Token "wil" is invalid.
So, since the default mode is JSON, escaped double quotes break the json format. I change the method into CSV by:
COPY temp_table FROM STDIN WITH CSV
Now, the "," delimiter is problematic so I change it by
COPY temp_table FROM STDIN WITH CSV DELIMITER '|'
Now, it throws another PSQLException:
ERROR: invalid input syntax for type json
Detail: Token "id" is invalid.
Apparently it switches into JSON mode again.
How can I copy these JSON formatted lines in CSV file into a temporary table ?
Based on the two lines we can see, the only special (to the COPY) character in the data is the newline character. Every thing else should be taken literally, including commas, double quotes and backslashes (all of which are then special to JSON, but must not be to COPY). Unfortunately there is no direct way to tell COPY to do this. You will need to assign the special characters to be ones that don't appear in your data set. Since the escape character cannot be changed from \ in text mode you need to abuse the CSV mode to do this. Assuming neither tab nor backspace appear in your data, that would leave you with something like this:
copy temp_table from stdin with (format csv, quote E'\t', delimiter E'\b');