Horton

Re: Emitting JSON to file using COPY TO

Joe Conway <mail@joeconway.com>

View thread
From: Joe Conway <mail@joeconway.com>
To: Nathan Bossart <nathandbossart@gmail.com>, Tom Lane <tgl@sss.pgh.pa.us>
Cc: Davin Shearer <davin@apache.org>, PostgreSQL-development <pgsql-hackers@postgreSQL.org>
Date: 2023-12-03T14:53:49Z
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>

Attachments

On 12/2/23 17:37, Joe Conway wrote:
> On 12/2/23 16:53, Nathan Bossart wrote:
>> On Sat, Dec 02, 2023 at 10:11:20AM -0500, Tom Lane wrote:
>>> So if you are writing a production that might need to match
>>> FORMAT followed by JSON, you need to match FORMAT_LA too.
>> 
>> Thanks for the pointer.  That does seem to be the culprit.
>> 
>> diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
>> index d631ac89a9..048494dd07 100644
>> --- a/src/backend/parser/gram.y
>> +++ b/src/backend/parser/gram.y
>> @@ -3490,6 +3490,10 @@ copy_generic_opt_elem:
>>                   {
>>                       $$ = makeDefElem($1, $2, @1);
>>                   }
>> +            | FORMAT_LA copy_generic_opt_arg
>> +                {
>> +                    $$ = makeDefElem("format", $2, @1);
>> +                }
>>           ;
>>   
>>   copy_generic_opt_arg:
> 
> 
> Yep -- I concluded the same. Thanks Tom!

The attached implements the above repair, as well as adding support for 
array decoration (or not) and/or comma row delimiters when not an array.

This covers the three variations of json import/export formats that I 
have found after light searching (SQL Server and DuckDB).

Still lacks and documentation, tests, and COPY FROM support, but here is 
what it looks like in a nutshell:

8<-----------------------------------------------
create table foo(id int8, f1 text, f2 timestamptz);
insert into foo
   select g.i,
          'line: ' || g.i::text,
          clock_timestamp()
   from generate_series(1,4) as g(i);

copy foo to stdout (format json);
{"id":1,"f1":"line: 1","f2":"2023-12-01T12:58:16.776863-05:00"}
{"id":2,"f1":"line: 2","f2":"2023-12-01T12:58:16.777084-05:00"}
{"id":3,"f1":"line: 3","f2":"2023-12-01T12:58:16.777096-05:00"}
{"id":4,"f1":"line: 4","f2":"2023-12-01T12:58:16.777103-05:00"}

copy foo to stdout (format json, force_array);
[
  {"id":1,"f1":"line: 1","f2":"2023-12-01T12:58:16.776863-05:00"}
,{"id":2,"f1":"line: 2","f2":"2023-12-01T12:58:16.777084-05:00"}
,{"id":3,"f1":"line: 3","f2":"2023-12-01T12:58:16.777096-05:00"}
,{"id":4,"f1":"line: 4","f2":"2023-12-01T12:58:16.777103-05:00"}
]

copy foo to stdout (format json, force_row_delimiter);
  {"id":1,"f1":"line: 1","f2":"2023-12-01T12:58:16.776863-05:00"}
,{"id":2,"f1":"line: 2","f2":"2023-12-01T12:58:16.777084-05:00"}
,{"id":3,"f1":"line: 3","f2":"2023-12-01T12:58:16.777096-05:00"}
,{"id":4,"f1":"line: 4","f2":"2023-12-01T12:58:16.777103-05:00"}

copy foo to stdout (force_array);
ERROR:  COPY FORCE_ARRAY requires JSON mode

copy foo to stdout (force_row_delimiter);
ERROR:  COPY FORCE_ROW_DELIMITER requires JSON mode
8<-----------------------------------------------


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