I have some data that looks like this:

1,"some text here", 22, "[{'id': 'NfKA3', 'u': 'https://somewebsite.com'}]"
2, "more text", 83, "[{'id': 'W3nAl', 'u': 'https://anotherwebsite.com'}]"
3, "even more text", 14, "[{'id': 'CyrMj', 'u': 'https://yetanotherwebsite.com'}]"

I'm trying to insert this data into a table that looks like this:

id integer, 
body varchar, 
count, varchar, 
url JSON

Using this command:

\copy mytable FROM 'myData.csv' WITH DELIMITER ',' csv header;

However, I'm having trouble with the single quotes in the url field of the csv. I'm getting errors like this:

ERROR:  invalid input syntax for type json
DETAIL:  Token "'" is invalid.
CONTEXT:  JSON data, line 1: [{'...

Can someone show me the correct way to structure my \COPY command so as to be able to load the url field in the csv as a JSON?

If necessary I can do some pre-processing of the file at the command line.


JSON uses double quote " to delimite keys and strings:

[{"id": "NfKA3", "u": "https://somewebsite.com"}]

CSV uses double quote to escape double quotes "" means "

1,"some text here", 22, "[{""id"": ""NfKA3"", ""u"": ""https://somewebsite.com""}]"

Look at: https://stackoverflow.com/questions/17808511/properly-escape-a-double-quote-in-csv


Use a database script instead of a csv:

with v (id,bo,c,url) as 
( 1,'some text here', 22, cast('[{ "id": "NfKA3", "u": "https://somewebsite.com"}]' as json)),
( 2,'some text here', 83, cast('[{ "id": "W3nAl", "u": "https://somewebsite.com"}]' as json))
insert into myTable select * from v;
  • Personally, I find the :: systax more readable... (values ( 1,'some text here', 22, '[{ "id": "NfKA3", "u": "somewebsite.com"}]'::JSONB); YMMV... – Vérace May 8 at 6:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.