From: Joe Conway <mail@joeconway.com>
To: Andrew Dunstan <andrew@dunslane.net>
To: Davin Shearer <davin@apache.org>
To: PostgreSQL-development <pgsql-hackers@postgresql.org>
Subject: Re: Emitting JSON to file using COPY TO
Date: Tue, 5 Dec 2023 16:15:29 -0500
Message-ID: <86650b9d-ff52-4ec8-964d-4b9352971ecd@joeconway.com> (raw)
In-Reply-To: <22d9e40d-7de2-1301-3970-16136f7732a6@dunslane.net>
References: <CALvfUkBxTYy5uWPFVwpk_7ii2zgT07t3d-yR_cy4sfrrLU=kcg@mail.gmail.com>
<b604b474-e2b2-4ba0-bc60-fd0cbc05cfc9@joeconway.com>
<9c77b6fa-ee88-b2e6-0fa7-4fc81721da35@dunslane.net>
<c30fc5da-0348-4e44-b699-804ccbdcfc36@joeconway.com>
<41dcba92-1075-e5e5-cb99-36711abf6cec@dunslane.net>
<bcbb2a04-5df3-49f2-b304-28ae8edc5189@joeconway.com>
<CALvfUkB2QtG-BXsTwjoLO8ZYUsNFytqjPvZex8stQ0r_qNM7HQ@mail.gmail.com>
<5c84b70b-ba18-c45d-dbbe-612fa229b2ce@dunslane.net>
<CALvfUkDxgTppjy-Q_i2znakMFp2vrJdPUEJNyGTL2LcZBOWV6A@mail.gmail.com>
<cbd65026-d79c-40b1-8be3-10e1db2fc092@joeconway.com>
<398c22f6-4299-4b17-80bf-2f14f4afd592@joeconway.com>
<CALvfUkAbj4Bwg0FjYpvj0-zddcetWg1Sejt=OGBTDskbqOFS_A@mail.gmail.com>
<46cc4507-a0d9-4044-b2ce-5a8bca8015c0@joeconway.com>
<CALvfUkD8+bcNs1cYeU3wK9YsyZ7zUjs2aTxO+++Vq7zKeUE7+w@mail.gmail.com>
<2554e520-e103-8978-dcb5-807dfeb77402@dunslane.net>
<cb1f118c-4698-4451-9ba9-dd8595f06c37@joeconway.com>
<22d9e40d-7de2-1301-3970-16136f7732a6@dunslane.net>
On 12/5/23 16:12, Andrew Dunstan wrote:
>
> On 2023-12-05 Tu 16:02, Joe Conway wrote:
>> On 12/5/23 15:55, Andrew Dunstan wrote:
>>>
>>> On 2023-12-05 Tu 14:50, Davin Shearer wrote:
>>>> Hi Joe,
>>>>
>>>> In reviewing the 005 patch, I think that when used with FORCE ARRAY,
>>>> we should also _imply_ FORCE ROW DELIMITER. I can't envision a use
>>>> case where someone would want to use FORCE ARRAY without also using
>>>> FORCE ROW DELIMITER. I can, however, envision a use case where
>>>> someone would want FORCE ROW DELIMITER without FORCE ARRAY, like
>>>> maybe including into a larger array. I definitely appreciate these
>>>> options and the flexibility that they afford from a user perspective.
>>>>
>>>> In the test output, will you also show the different variations with
>>>> FORCE ARRAY and FORCE ROW DELIMITER => {(false, false), (true,
>>>> false), (false, true), (true, true)}? Technically you've already
>>>> shown me the (false, false) case as those are the defaults.
>>>>
>>>>
>>>
>>> I don't understand the point of FORCE_ROW_DELIMITER at all. There is
>>> only one legal delimiter of array items in JSON, and that's a comma.
>>> There's no alternative and it's not optional. So in the array case you
>>> MUST have commas and in any other case (e.g. LINES) I can't see why you
>>> would have them.
>>
>> The current patch already *does* imply row delimiters in the array
>> case. It says so here:
>> 8<---------------------------
>> + <varlistentry>
>> + <term><literal>FORCE_ARRAY</literal></term>
>> + <listitem>
>> + <para>
>> + Force output of array decorations at the beginning and end of
>> output.
>> + This option implies the <literal>FORCE_ROW_DELIMITER</literal>
>> + option. It is allowed only in <command>COPY TO</command>, and
>> only
>> + when using <literal>JSON</literal> format.
>> + The default is <literal>false</literal>.
>> + </para>
>> 8<---------------------------
>>
>> and it does so here:
>> 8<---------------------------
>> + if (opts_out->force_array)
>> + opts_out->force_row_delimiter = true;
>> 8<---------------------------
>>
>> and it shows that here:
>> 8<---------------------------
>> + copy copytest to stdout (format json, force_array);
>> + [
>> + {"style":"DOS","test":"abc\r\ndef","filler":1}
>> + ,{"style":"Unix","test":"abc\ndef","filler":2}
>> + ,{"style":"Mac","test":"abc\rdef","filler":3}
>> + ,{"style":"esc\\ape","test":"a\\r\\\r\\\n\\nb","filler":4}
>> + ]
>> 8<---------------------------
>>
>> It also does not allow explicitly setting row delimiters false while
>> force_array is true here:
>> 8<---------------------------
>>
>> + if (opts_out->force_array &&
>> + force_row_delimiter_specified &&
>> + !opts_out->force_row_delimiter)
>> + ereport(ERROR,
>> + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
>> + errmsg("cannot specify FORCE_ROW_DELIMITER
>> false with FORCE_ARRAY true")));
>> 8<---------------------------
>>
>> Am I understanding something incorrectly?
>
>
> But what's the point of having it if you're not using FORCE_ARRAY?
See the follow up email -- other databases support it so why not? It
seems to be a thing...
--
Joe Conway
PostgreSQL Contributors Team
RDS Open Source Databases
Amazon Web Services: https://aws.amazon.com
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-general@postgresql.org
Cc: mail@joeconway.com, andrew@dunslane.net, davin@apache.org, pgsql-hackers@postgresql.org
Subject: Re: Emitting JSON to file using COPY TO
In-Reply-To: <86650b9d-ff52-4ec8-964d-4b9352971ecd@joeconway.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox