PostgreSQL COPY cannot read JSON from CSV file

Question

I'm copying data from a CSV file into a PostgreSQL table using COPY

My CSV file is simply:

0\"a string"

And my table "Test" was created by the following:

create table test (
id integer,
data jsonb
);

My copy statement was the following:

I received the following error:

williazz=# \copy test from 'test/test.csv' delimiters '\' CSV
ERROR:  invalid input syntax for type json
DETAIL:  Token "a" is invalid.
CONTEXT:  JSON data, line 1: a...
COPY test, line 1, column data: "a string"

Interestingly, when I changed my CSV file to a number, it had no problem.

CSV: 0\1505

williazz=# \copy test from 'test/test.csv' delimiters '\' CSV
COPY 1
williazz=# select * from test;
 id | data 
----+------
  0 | 1505
(1 row)

Furthermore, numbers in arrays also work:

CSV:

1\[0,1,2,3,4,5]

williazz=# select * from test;
 id |     data      
----+---------------
  0 | 1505
  1 | [0,1,2,3,4,5]
(2 rows)

But as soon as I introduct a non-digit string into the JSON, the COPY stops working

0\[1,2,"three",4,5]

ERROR:  invalid input syntax for type json
DETAIL:  Token "three" is invalid.
CONTEXT:  JSON data, line 1: [1, 2, three...
COPY test, line 1, column data: "[1, 2, three, 4, 5]"

I cannot get postgres to read a non-digit string in JSON format. I've also tried changing the data type of column "data" from jsonb to json, and using basically every combination of single and double quotes

Could someone please help me identify the problem? Thank you

The content of the file is not valid json. So you need to format it like {"name":"Claus", "born": 1967}. There are online validators you can try prior to importing like jsonformatter.curiousconcept.com. — kometen
– kometen, Commented Jul 29, 2019 at 22:04
Thanks for your quick response. I've already tried simply writing "some string", which is valid JSON, but I was met with the same error. Sorry, I should have included that in my post and have just editted it — democrenes
– democrenes, Commented Jul 29, 2019 at 22:11

Schwern · Accepted Answer · 2019-07-29 22:16:44Z

3

Because your file is CSV encoded, it does not mean what you think.

0\"a string"

With a delimiter of \ this is two values: the number 0 and the string a string. Note the lack of quotes. Those quotes are part of the CSV string formatting. a string is not valid JSON, the quotes are required.

Instead you need to include the JSON string quotes inside the CSV string quotes. Quotes in CSV are escaped by doubling them.

0\"""a string"""

Now that is the number 0 and the string "a string" including quotes.

And as an observation, it would be simpler to remove the complication of embedding JSON into a CSV and use a pure JSON file.

[
  [0, "a string"],
  [1, "other string"]
]

answered Jul 29, 2019 at 22:16

Schwern

167k28 gold badges226 silver badges371 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

PostgreSQL COPY cannot read JSON from CSV file

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related