How to quote arrays in postgres copy statement to inser into JSON field I have some data that looks like this:
id,bo,c,url
1,&quot;some text here&quot;, 22, &quot;[{'id': 'NfKA3', 'u': 'https://somewebsite.com'}]&quot;
2, &quot;more text&quot;, 83, &quot;[{'id': 'W3nAl', 'u': 'https://anotherwebsite.com'}]&quot;
3, &quot;even more text&quot;, 14, &quot;[{'id': 'CyrMj', 'u': 'https://yetanotherwebsite.com'}]&quot;

I'm trying to insert this data into a table that looks like this:
CREATE TABLE myTable(
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 &quot;'&quot; 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 &quot; to delimite keys and strings:
[{&quot;id&quot;: &quot;NfKA3&quot;, &quot;u&quot;: &quot;https://somewebsite.com&quot;}]

CSV uses double quote to escape double quotes &quot;&quot; means &quot;
1,&quot;some text here&quot;, 22, &quot;[{&quot;&quot;id&quot;&quot;: &quot;&quot;NfKA3&quot;&quot;, &quot;&quot;u&quot;&quot;: &quot;&quot;https://somewebsite.com&quot;&quot;}]&quot;

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