Horton

Re: Emitting JSON to file using COPY TO

Daniel Verite <daniel@manitou-mail.org>

View thread
From: "Daniel Verite" <daniel@manitou-mail.org>
To: "Dave Cramer" <davecramer@postgres.rocks>
Cc: David G. Johnston <david.g.johnston@gmail.com>, Joe Conway <mail@joeconway.com>, Andrew Dunstan <andrew@dunslane.net>, Davin Shearer <davin@apache.org>, PostgreSQL-development <pgsql-hackers@postgresql.org>
Date: 2023-12-08T20:35:39Z
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>

	Dave Cramer wrote:

> > This argument for leaving 3 as the column count makes sense to me.  I
> > agree this content is not meant to facilitate interpreting the contents at
> > a protocol level.
> >
> 
> I'd disagree. From my POV if the data comes back as a JSON Array this is
> one object and this should be reflected in the column count.

The doc says this:
"Int16
    The number of columns in the data to be copied (denoted N below)."

and this formulation is repeated in PQnfields() for libpq:

"PQnfields
    Returns the number of columns (fields) to be copied."

How to interpret that sentence? 
"to be copied" from what, into what, and by what way?

A plausible interpretation is "to be copied from the source data
into the COPY stream, by the backend".	So the number of columns
to be copied likely refers to the columns of the dataset, not the
"in-transit form" that is text or csv or json.

The interpetation you're proposing also makes sense, that it's just
one json column per row, or even a single-row single-column for the
entire dataset in the force_array case, but then the question is why
isn't that number of columns always 1 for the original "text" format,
since each row is represented in the stream as a single long piece of
text?


Best regards,
-- 
Daniel Vérité
https://postgresql.verite.pro/
Twitter: @DanielVerite