MIME-Version: 1.0
References: <CALvfUkBxTYy5uWPFVwpk_7ii2zgT07t3d-yR_cy4sfrrLU=kcg@mail.gmail.com>
 <fc1e39f8-274f-4be6-8a70-9fb0d9fe84a2@aklaver.com>
In-Reply-To: <fc1e39f8-274f-4be6-8a70-9fb0d9fe84a2@aklaver.com>
From: Dominique Devienne <ddevienne@gmail.com>
Date: Mon, 27 Nov 2023 10:33:00 +0100
Message-ID: <CAFCRh-_LhP5Ln4-beVz=1pQ9VycU3hAxnJHXhPHZRmX0BSCVEQ@mail.gmail.com>
Subject: Re: Emitting JSON to file using COPY TO
To: Adrian Klaver <adrian.klaver@aklaver.com>
Cc: Davin Shearer <scholarsmate@gmail.com>, pgsql-general@lists.postgresql.org
Content-Type: multipart/alternative; boundary="0000000000009a83be060b1efdd0"
Archived-At: <https://www.postgresql.org/message-id/CAFCRh-_LhP5Ln4-beVz%3D1pQ9VycU3hAxnJHXhPHZRmX0BSCVEQ%40mail.gmail.com>
Precedence: bulk

--0000000000009a83be060b1efdd0
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

On Sat, Nov 25, 2023 at 10:00=E2=80=AFPM Adrian Klaver <adrian.klaver@aklav=
er.com>
wrote:

> On 11/25/23 11:21, Davin Shearer wrote:
> > Hello!
> >
> > I'm trying to emit a JSON aggregation of JSON rows to a file using COPY
> > TO, but I'm running into problems with COPY TO double quoting the
> > output.   Here is a minimal example that demonstrates the problem I'm
> > having:
> >
>
> > I have tried to get COPY TO to copy the results to file "as-is" by
> > setting the escape and the quote characters to the empty string (''),
> > but they only apply to the CSV format.
> >
> > Is there a way to emit JSON results to file from within postgres?
> > Effectively, nn "as-is" option to COPY TO would work well for this JSON
> > use case.
> >
>
> Not using COPY.
>
> See David Johnson's post for one way using the client psql.
>
> Otherwise you will need to use any of the many ETL programs out there
> that are designed for this sort of thing.
>

Guys, I don't get answers like that. The JSON spec is clear:

>

--0000000000009a83be060b1efdd0
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div dir=3D"ltr">On Sat, Nov 25, 2023 at 10:00=E2=80=AFPM =
Adrian Klaver &lt;<a href=3D"mailto:adrian.klaver@aklaver.com">adrian.klave=
r@aklaver.com</a>&gt; wrote:<br></div><div class=3D"gmail_quote"><blockquot=
e class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px s=
olid rgb(204,204,204);padding-left:1ex">On 11/25/23 11:21, Davin Shearer wr=
ote:<br>
&gt; Hello!<br>
&gt; <br>
&gt; I&#39;m trying to emit a JSON aggregation of JSON rows to a file using=
 COPY <br>
&gt; TO, but I&#39;m running into problems with COPY TO double quoting the =
<br>
&gt; output. =C2=A0 Here is a minimal example that demonstrates the problem=
 I&#39;m <br>
&gt; having:<br>
&gt; <br>
<br>
&gt; I have tried to get COPY TO to copy the results to file &quot;as-is&qu=
ot; by <br>
&gt; setting the escape and the quote characters to the empty string (&#39;=
&#39;), <br>
&gt; but they only apply to the CSV format.<br>
&gt; <br>
&gt; Is there a way to emit JSON results to file from within postgres?=C2=
=A0 <br>
&gt; Effectively, nn &quot;as-is&quot; option to COPY TO would work well fo=
r this JSON <br>
&gt; use case.<br>
&gt; <br>
<br>
Not using COPY.<br>
<br>
See David Johnson&#39;s post for one way using the client psql.<br>
<br>
Otherwise you will need to use any of the many ETL programs out there <br>
that are designed for this sort of thing.<br></blockquote><div><br></div><d=
iv>Guys, I don&#39;t get answers like that. The JSON spec is clear:</div><d=
iv><br></div><div>&gt;=C2=A0=C2=A0</div></div></div>

--0000000000009a83be060b1efdd0--