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 ?

CodePudding user response：

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;"