Re: Emitting JSON to file using COPY TO

From: Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>
To: Davin Shearer <scholarsmate(at)gmail(dot)com>, pgsql-general(at)lists(dot)postgresql(dot)org
Subject: Re: Emitting JSON to file using COPY TO
Date: 2023-11-25 21:00:12
Message-ID: fc1e39f8-274f-4be6-8a70-9fb0d9fe84a2@aklaver.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general pgsql-hackers

On 11/25/23 11:21, Davin Shearer wrote:
> Hello!
>
> I'm trying to emit a JSON aggregation of JSON rows to a file using COPY
> TO, but I'm running into problems with COPY TO double quoting the
> output.   Here is a minimal example that demonstrates the problem I'm
> having:
>

> I have tried to get COPY TO to copy the results to file "as-is" by
> setting the escape and the quote characters to the empty string (''),
> but they only apply to the CSV format.
>
> Is there a way to emit JSON results to file from within postgres?
> Effectively, nn "as-is" option to COPY TO would work well for this JSON
> use case.
>

Not using COPY.

See David Johnson's post for one way using the client psql.

Otherwise you will need to use any of the many ETL programs out there
that are designed for this sort of thing.

> Any assistance would be appreciated.
>
> Thanks,
> Davin

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Peter J. Holzer 2023-11-26 00:22:40 Re: Can user specification of a column value be required when querying a view ?
Previous Message David G. Johnston 2023-11-25 20:02:46 Re: Emitting JSON to file using COPY TO

Browse pgsql-hackers by date

  From Date Subject
Next Message Ashwin Agrawal 2023-11-25 22:33:52 Re: brininsert optimization opportunity
Previous Message Alexander Korotkov 2023-11-25 20:46:47 Re: [PATCH] Tracking statements entry timestamp in pg_stat_statements