-1

I am trying to export a column type jsonb using the copy command as follow:

payload
-------
{"test": "testing"}

sudo -u postgres psql test_database -c "COPY (SELECT payload FROM test_table) TO STDIN CSV"

The output gives me quoted text which is not a correct json format:

"{""test"":""testing""}"

How can I get a correct json format ?

pikk
  • 837
  • 5
  • 21
  • 38
  • It looks like you've chosen [CSV output format](https://www.postgresql.org/docs/14/sql-copy.html#id-1.9.3.55.9.3), which escapes quotes that way. `COPY` does not produce JSON. – Bergi Jun 16 '22 at 13:22
  • For JSON embedded in CSV, that *is* the correct format. – jjanes Jun 16 '22 at 14:52

1 Answers1

1

You've chosen CSV output format, which escapes quotes that way. COPY does not produce JSON.

Do not use COPY to get the output, rather see store postgresql result in bash variable or How to return a value from psql to bash and use it?:

psql -U postgres -d test_database -AXqtc "SELECT payload FROM test_table;"
Bergi
  • 630,263
  • 148
  • 957
  • 1,375
  • Is it possible to set the delimiter and NULL values ? i.e DELIMITER '~' NULL 'null' ? – pikk Jun 16 '22 at 13:33
  • @pikk For `COPY`? Yes, but it'll still use escaping for it. – Bergi Jun 16 '22 at 13:49
  • No i mean with your solution... – pikk Jun 16 '22 at 13:50
  • 1
    You can `COALESCE(payload, 'null'::jsonb)`. And I think there's no delimiter if you select only a single column? – Bergi Jun 16 '22 at 13:51
  • If i SELECT more columns , is it possible to set DELIMITER? – pikk Jun 16 '22 at 13:53
  • According to [the `psql` docs](https://www.postgresql.org/docs/current/app-psql.html), `-F` (and `-z`) do that. Apparently you can also run `\pset null 'null'; SELECT …;` to change the `NULL` output – Bergi Jun 16 '22 at 13:57