CODEWITHSUNDEEP
jsonpostgresqlcsvpsqlpostgresql-9.4
lemon master
lemon master

Reputation: 219

Using \COPY to load CSV with JSON fields into Postgres

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

Upvotes: 9

Views: 11557

Answers (3)

andilabs
andilabs

Reputation: 23301

The answer of Aeblisto almost did the trick for my crazy JSON fields, but needed to modify an only small bit - THE QUOTE with backslash - here it is in full form:

COPY "your_schema_name.yor_table_name" (your, column_names, here) 
FROM STDIN 
WITH CSV DELIMITER E'\t' QUOTE E'\b' ESCAPE '\';
--here rows data
\.

Upvotes: 2

Abelisto
Abelisto

Reputation: 15624

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 '\'

Upvotes: 12

teppic
teppic

Reputation: 7286

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}

Upvotes: 1

Related Questions