Horton

Re: Emitting JSON to file using COPY TO

Davin Shearer <davin@apache.org>

View thread
From: Davin Shearer <davin@apache.org>
To: "pgsql-general@lists.postgresql.org" <pgsql-general@lists.postgresql.org>
Date: 2023-11-29T15:32:54Z
Lists: pgsql-hackers

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. Add option force_array for COPY JSON FORMAT

  2. json format for COPY TO

  3. introduce CopyFormat, refactor CopyFormatOptions

  4. Doc: add IDs to copy.sgml's <varlistentry> and <refsect1>

Thanks for the responses everyone.

I worked around the issue using the `psql -tc` method as Filip described.

I think it would be great to support writing JSON using COPY TO at
some point so I can emit JSON to files using a PostgreSQL function directly.

-Davin

On Tue, Nov 28, 2023 at 2:36 AM Filip Sedlák <filip@sedlakovi.org> wrote:

> This would be a very special case for COPY. It applies only to a single
> column of JSON values. The original problem can be solved with psql
> --tuples-only as David wrote earlier.
>
>
> $ psql -tc 'select json_agg(row_to_json(t))
>               from (select * from public.tbl_json_test) t;'
>
>   [{"id":1,"t_test":"here's a \"string\""}]
>
>
> Special-casing any encoding/escaping scheme leads to bugs and harder
> parsing.
>
> Just my 2c.
>
> --
> Filip Sedlák
>