Horton

Re: Emitting JSON to file using COPY TO

Joe Conway <mail@joeconway.com>

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

On 12/6/23 19:39, David G. Johnston wrote:
> On Wed, Dec 6, 2023 at 4:45 PM Joe Conway <mail@joeconway.com 
> <mailto:mail@joeconway.com>> wrote:
> 
> 
>     " The backend sends a CopyOutResponse message to the frontend, followed
>          by zero or more CopyData messages (always one per row), followed by
>          CopyDone"
> 
>     probably "always one per row" would be changed to note that json array
>     format outputs two extra rows for the start/end bracket.
> 
> 
> Fair, I was ascribing much more semantic meaning to this than it wants.
> 
> I don't see any real requirement, given the lack of semantics, to 
> mention JSON at all.  It is one CopyData per row, regardless of the 
> contents.  We don't delineate between the header and non-header data in 
> CSV.  It isn't a protocol concern.

good point

> But I still cannot shake the belief that using a format code of 1 - 
> which really could be interpreted as meaning "textual csv" in practice - 
> for this JSON output is unwise and we should introduce a new integer 
> value for the new fundamental output format.

No, I am pretty sure you still have that wrong. The "1" means binary 
mode. As in
8<----------------------
FORMAT

     Selects the data format to be read or written: text, csv (Comma 
Separated Values), or binary. The default is text.
8<----------------------

That is completely separate from text and csv. It literally means to use 
the binary output functions instead of the usual ones:

8<----------------------
         if (cstate->opts.binary)
             getTypeBinaryOutputInfo(attr->atttypid,
                                     &out_func_oid,
                                     &isvarlena);
         else
             getTypeOutputInfo(attr->atttypid,
                               &out_func_oid,
                               &isvarlena);
8<----------------------

Both "text" and "csv" mode use are non-binary output formats. I believe 
the JSON output format is also non-binary.

-- 
Joe Conway
PostgreSQL Contributors Team
RDS Open Source Databases
Amazon Web Services: https://aws.amazon.com