Đỗ Ngọc Trí Cường wrote: > I want to export it to a file in JSON format so I run the query as below: > COPY (SELECT...) TO '/home/postgres/test1.json' COPY TO applies its own format rules on top of the data, which include among other things, quoting the newline characters. What you seem to expect is to copy a single-row, single-column piece of data verbatim to the output file, but COPY does not offer that. Such an option has already been discussed among developers, for instance in this thread as a 'RAW' format: https://www.postgresql.org/message-id/CAFj8pRAfer%2Bip3JCMTnvzra2QK7W9hk0hw1YqE8%2B%2BPZA1OqSJw%40mail.gmail.com but eventually it was not implemented. The unformatted output can be obtained client-side without COPY: psql -Atc "SELECT row_to_json(...)" > /path/to/file If you really need it server-side, a function that can write a result to a file is required. I don't think it exists as such out of the box, but you may either: - write your own function in any untrusted language to do just that (open file, write the piece of text into it, close it). - or see pg_file_write() from the adminpack contrib module https://www.postgresql.org/docs/current/static/adminpack.html With that, you may call, as a superuser: select pg_file_write('path/to/file.json', (select row_to_json(...))::text, false); But note that the path must be relative to the PGDATA directory. - or export the data as an intermediary large object at the cost of some data churn in the large objects. And in that case, the path is not constrained to postgres data directory. do $$ declare id oid; j json; begin j := (select row_to_json(t) from <your query here>); id := lo_from_bytea(0, convert_to(j::text, 'UTF-8')); perform lo_export(id, '/path/to/file.json'); perform lo_unlink(id); end; $$ language plpgsql; For all these server-side methods, you need to be superuser, just like for COPY TO file anyway. Best regards, -- Daniel Vérité PostgreSQL-powered mailer: http://www.manitou-mail.org Twitter: @DanielVerite
