Using \COPY to load CSV with JSON fields into Postgres

Question

I'm attempting to load TSV data from a file into a Postgres table using the \COPY command.

Here's an example data row:

2017-11-22 23:00:00     "{\"id\":123,\"class\":101,\"level\":3}"

Here's the psql command I'm using:

\COPY bogus.test_table (timestamp, sample_json) FROM '/local/file.txt' DELIMITER E'\t'

Here's the error I'm receiving:

ERROR:  invalid input syntax for type json
DETAIL:  Token "sample_json" is invalid.
CONTEXT:  JSON data, line 1: "{"sample_json...
COPY test_table, line 1, column sample_json: ""{\"id\":123,\"class\":101,\"level\":3}""

I verified the JSON is in the correct JSON format and read a couple similar questions, but I'm still not sure what's going on here. An explanation would be awesome

Answer 1

To load your data file as it is:

\COPY bogus.test_table (timestamp, sample_json) FROM '/local/file.txt' CSV DELIMITER E'\t' QUOTE '"' ESCAPE '\'

Answer 2

Your json is quoted. It shouldn't have surrounding " characters, and the " characters surrounding the field names shouldn't be escaped.

It should look like this:

2017-11-22 23:00:00 {"id":123,"class":101,"level":3}