I want to export using copy 5 columns of a table, 3 of them contain ID, text and timestamp values but 2 of them contain JSON and GeoJSON elements, e.g.:

 // Please, note the non-quoted string Value1
 // when there is no space in the value itself:
{"key": "value with space", "other_key": Value1, ...}

but when using this command:

psql \
  -d <connection_uri> \
  --command "\copy public.mytable (id, json_array, geojson_field, status, timestamp) TO './file.csv' DELIMITER ';' CSV HEADER ENCODING 'UTF8' QUOTE '\"' ;"

The result looks strange:

11784,"{'"First value'",'"Second value'", ThirdValue, '"Fourth val'",...

i.e. it's placing quote in a strange order: "single-double <field_value> single-double"

(also I don't get the headers...)

I wish I could end with a true JSON object in the csv file that I can copy/paste in a validator, e.g. here without errors.

Am I doing something wrong?
If yes, how to fix it?


  • What version of PostgreSQL is this? Jan 19, 2022 at 17:02

2 Answers 2

 copy t_jsonb to STDOUT (FORMAT CSV, QUOTE ' ', header true);
  • I went into a strange error: ERROR: relation "t_jsonb" does not exist
    – s.k
    Jan 20, 2022 at 10:28
  • Yes, very strange. Perhaps you have no table "t_jsonb" and yours is called "mytable"? Jan 20, 2022 at 10:33

I end up using it this way, using FORCE_QUOTE, which actually works by placing 2 double quotes around the JSON keys and values where needed:

psql -d <connection_uri> \
  --command "\copy public.mytable (id, json_array, geojson_field, status, timestamp) TO './file.csv' WITH (FORMAT CSV, HEADER TRUE, DELIMITER ';', ENCODING 'UTF8', QUOTE '\"', FORCE_QUOTE (json_array, geojson_field));"

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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