From: Chao Li <li.evan.chao@gmail.com>
Message-Id: <3E68D1C4-8DA9-437F-B05A-DEB74782676F@gmail.com>
Content-Type: multipart/mixed;
	boundary="Apple-Mail=_0B011C42-9154-42CE-9943-ECBFA51C3D51"
Mime-Version: 1.0 (Mac OS X Mail 16.0 \(3864.400.21\))
Subject: Re: COPY JSON: use trailing commas in FORCE_ARRAY output
Date: Wed, 6 May 2026 17:25:38 +0800
In-Reply-To: <5f99e4a9-efa1-4458-b829-e7a7ff366f08@gmail.com>
Cc: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
To: Alex Guo <guo.alex.hengchen@gmail.com>
References: <DFAC4097-2559-4DED-B7D5-EB53B02E9DA3@gmail.com>
 <5f99e4a9-efa1-4458-b829-e7a7ff366f08@gmail.com>
Archived-At: 
 <https://www.postgresql.org/message-id/3E68D1C4-8DA9-437F-B05A-DEB74782676F%40gmail.com>
Precedence: bulk


--Apple-Mail=_0B011C42-9154-42CE-9943-ECBFA51C3D51
Content-Transfer-Encoding: quoted-printable
Content-Type: text/plain;
	charset=us-ascii


> On May 6, 2026, at 16:46, Alex Guo <guo.alex.hengchen@gmail.com> =
wrote:
>=20
>=20
> On 5/6/26 2:40 PM, Chao Li wrote:
>> Hi,
>>=20
>> Another issue I found with COPY TO (FORMAT json, FORCE_ARRAY) is that =
it places the delimiter comma at the beginning of the next line, like =
this:
>> ```
>> evantest=3D# copy test_json_copy to stdout with (format json, =
force_array);
>> [
>> =
{"id":1,"name":"Alice","is_active":true,"tags":["dev","db"],"created_at":"=
2026-05-01"}
>> =
,{"id":2,"name":"Bob","is_active":false,"tags":["manager"],"created_at":"2=
026-05-02"}
>> =
,{"id":3,"name":"Charlie","is_active":null,"tags":null,"created_at":null}
>> ,{"id":4,"name":"Special Case: =
\"Quotes\"","is_active":true,"tags":["a","b"],"created_at":"2026-05-04"}
>> ]
>> ```
>>=20
>> I was surprised by the comma placement. It is valid JSON, but it =
looks quite uncommon.
>>=20
>> For comparison, the existing json_agg() places commas at the end of =
the line:
>> ```
>> evantest=3D# select json_agg(t) from (select id, name from =
test_json_copy) t;
>>                    json_agg
>> ----------------------------------------------
>>  [{"id":1,"name":"Alice"},                   +
>>   {"id":2,"name":"Bob"},                     +
>>   {"id":3,"name":"Charlie"},                 +
>>   {"id":4,"name":"Special Case: \"Quotes\""}]
>> (1 row)
>> ```
>>=20
>> If this feature had already been released, I would not think it worth =
changing just for formatting. But since "FORMAT json" is a new PG19 =
feature and has not been released yet, I think it is better to make the =
output to follow the more common style.
>>=20
>> This patch changes the output to place the comma at the end of the =
previous line instead. The fix only adjusts how commas and newlines are =
emitted. It does not buffer the whole result, so it should not have any =
performance impact.
>>=20
>> See the attached patch for details.
>>=20
>> Best regards,
>> --
>> Chao Li (Evan)
>> HighGo Software Co., Ltd.
>> https://www.highgo.com/
>>=20
>>=20
>>=20
> Thanks for the patch, I like it as I feel better with placing commas =
at the end of lines.
>=20
> I have a small suggestion. The function name CopySendTextLikeEOL reads =
very similar to the existing CopySendTextLikeEndOfRow. Would it better =
to rename it to CopySendTextLikeLineTerminator?
>=20
> Other than that, the patch looks good to me.
>=20
> Regards,
> Alex Guo

Thanks for the suggestion, I take it.

PFA v2 - Renamed CopySendTextLikeEOL to CopySendTextLikeLineTerminator.

Best regards,
--
Chao Li (Evan)
HighGo Software Co., Ltd.
https://www.highgo.com/


--Apple-Mail=_0B011C42-9154-42CE-9943-ECBFA51C3D51
Content-Disposition: attachment;
	filename=v2-0001-COPY-JSON-use-trailing-commas-in-FORCE_ARRAY-outp.patch
Content-Type: application/octet-stream;
	x-unix-mode=0644;
	name="v2-0001-COPY-JSON-use-trailing-commas-in-FORCE_ARRAY-outp.patch"
Content-Transfer-Encoding: quoted-printable

=46rom=204f2151348d89f9417d161729b5b717d32804d702=20Mon=20Sep=2017=20=
00:00:00=202001=0AFrom:=20"Chao=20Li=20(Evan)"=20<lic@highgo.com>=0A=
Date:=20Wed,=206=20May=202026=2014:14:03=20+0800=0ASubject:=20[PATCH=20=
v2]=20COPY=20JSON:=20use=20trailing=20commas=20in=20FORCE_ARRAY=20output=0A=
=0AChange=20COPY=20TO=20...=20FORMAT=20JSON,=20FORCE_ARRAY=20output=20to=20=
place=20commas=20at=0Athe=20end=20of=20each=20array=20element=20line,=20=
instead=20of=20at=20the=20beginning=20of=20the=0Anext=20line.=0A=0A=
Previously,=20output=20looked=20like=20this:=0A```=0A[=0A=20{"id":1}=0A=
,{"id":2}=0A]=0A```=0A=0AThis=20is=20valid=20JSON,=20but=20it=20is=20an=20=
unusual=20formatting=20style=20and=20can=20be=0Asurprising=20to=20=
readers.=20Make=20it=20emit=20the=20more=20conventional=20form=20=
instead:=0A```=0A[=0A=20{"id":1},=0A=20{"id":2}=0A]=0A```=0A=0AImplement=20=
this=20without=20buffering=20the=20whole=20result=20by=20adjusting=20how=20=
JSON=0Arows=20are=20terminated=20and=20how=20the=20separator=20is=20=
emitted=20between=20rows.=0A=0AUpdate=20the=20regression=20test=20output=20=
accordingly.=0A=0AAuthor:=20Chao=20Li=20<lic@highgo.com>=0AReviewed-by:=20=
Ayush=20Tiwari=20<ayushtiwari.slg01@gmail.com>=0AReviewed-by:=20Daniel=20=
Gustafsson=20<daniel@yesql.se>=0AReviewed-by:=20Alex=20Guo=20=
<guo.alex.hengchen@gmail.com>=0ADiscussion:=20=
https://postgr.es/m/DFAC4097-2559-4DED-B7D5-EB53B02E9DA3@gmail.com=0A---=0A=
=20src/backend/commands/copyto.c=20=20=20=20=20=20|=2029=20=
++++++++++++++++++++++++-----=0A=20src/test/regress/expected/copy.out=20=
|=2024=20++++++++++++------------=0A=202=20files=20changed,=2036=20=
insertions(+),=2017=20deletions(-)=0A=0Adiff=20--git=20=
a/src/backend/commands/copyto.c=20b/src/backend/commands/copyto.c=0A=
index=2085d15353647..e98a15dcd64=20100644=0A---=20=
a/src/backend/commands/copyto.c=0A+++=20b/src/backend/commands/copyto.c=0A=
@@=20-156,6=20+156,7=20@@=20static=20void=20CopySendData(CopyToState=20=
cstate,=20const=20void=20*databuf,=20int=20datasize);=0A=20static=20void=20=
CopySendString(CopyToState=20cstate,=20const=20char=20*str);=0A=20static=20=
void=20CopySendChar(CopyToState=20cstate,=20char=20c);=0A=20static=20=
void=20CopySendEndOfRow(CopyToState=20cstate);=0A+static=20void=20=
CopySendTextLikeLineTerminator(CopyToState=20cstate);=0A=20static=20void=20=
CopySendTextLikeEndOfRow(CopyToState=20cstate);=0A=20static=20void=20=
CopySendInt32(CopyToState=20cstate,=20int32=20val);=0A=20static=20void=20=
CopySendInt16(CopyToState=20cstate,=20int16=20val);=0A@@=20-349,6=20=
+350,8=20@@=20CopyToJsonEnd(CopyToState=20cstate)=0A=20{=0A=20=09if=20=
(cstate->opts.force_array)=0A=20=09{=0A+=09=09if=20=
(cstate->json_row_delim_needed)=0A+=09=09=09=
CopySendTextLikeLineTerminator(cstate);=0A=20=09=09CopySendChar(cstate,=20=
']');=0A=20=09=09CopySendTextLikeEndOfRow(cstate);=0A=20=09}=0A@@=20=
-418,7=20+421,11=20@@=20CopyToJsonOneRow(CopyToState=20cstate,=20=
TupleTableSlot=20*slot)=0A=20=09if=20(cstate->opts.force_array)=0A=20=09=
{=0A=20=09=09if=20(cstate->json_row_delim_needed)=0A+=09=09{=0A=20=09=09=09=
CopySendChar(cstate,=20',');=0A+=09=09=09=
CopySendTextLikeLineTerminator(cstate);=0A+=09=09=09CopySendChar(cstate,=20=
'=20');=0A+=09=09}=0A=20=09=09else=0A=20=09=09{=0A=20=09=09=09/*=20first=20=
row=20needs=20no=20delimiter=20*/=0A@@=20-429,7=20+436,10=20@@=20=
CopyToJsonOneRow(CopyToState=20cstate,=20TupleTableSlot=20*slot)=0A=20=0A=
=20=09CopySendData(cstate,=20cstate->json_buf->data,=20=
cstate->json_buf->len);=0A=20=0A-=09CopySendTextLikeEndOfRow(cstate);=0A=
+=09if=20(cstate->opts.force_array)=0A+=09=09CopySendEndOfRow(cstate);=0A=
+=09else=0A+=09=09CopySendTextLikeEndOfRow(cstate);=0A=20}=0A=20=0A=20/*=0A=
@@=20-641,11=20+651,10=20@@=20CopySendEndOfRow(CopyToState=20cstate)=0A=20=
}=0A=20=0A=20/*=0A-=20*=20Wrapper=20function=20of=20CopySendEndOfRow=20=
for=20text,=20CSV,=20and=20json=20formats.=20Sends=20the=0A-=20*=20line=20=
termination=20and=20do=20common=20appropriate=20things=20for=20the=20end=20=
of=20row.=0A+=20*=20Append=20the=20platform-appropriate=20line=20=
termination=20for=20text-like=20output.=0A=20=20*/=0A-static=20inline=20=
void=0A-CopySendTextLikeEndOfRow(CopyToState=20cstate)=0A+static=20void=0A=
+CopySendTextLikeLineTerminator(CopyToState=20cstate)=0A=20{=0A=20=09=
switch=20(cstate->copy_dest)=0A=20=09{=0A@@=20-664,6=20+673,16=20@@=20=
CopySendTextLikeEndOfRow(CopyToState=20cstate)=0A=20=09=09default:=0A=20=09=
=09=09break;=0A=20=09}=0A+}=0A+=0A+/*=0A+=20*=20Wrapper=20function=20of=20=
CopySendEndOfRow=20for=20text,=20CSV,=20and=20json=20formats.=20Sends=20=
the=0A+=20*=20line=20termination=20and=20do=20common=20appropriate=20=
things=20for=20the=20end=20of=20row.=0A+=20*/=0A+static=20inline=20void=0A=
+CopySendTextLikeEndOfRow(CopyToState=20cstate)=0A+{=0A+=09=
CopySendTextLikeLineTerminator(cstate);=0A=20=0A=20=09/*=20Now=20take=20=
the=20actions=20related=20to=20the=20end=20of=20a=20row=20*/=0A=20=09=
CopySendEndOfRow(cstate);=0Adiff=20--git=20=
a/src/test/regress/expected/copy.out=20=
b/src/test/regress/expected/copy.out=0Aindex=201714faab39c..3da23de8551=20=
100644=0A---=20a/src/test/regress/expected/copy.out=0A+++=20=
b/src/test/regress/expected/copy.out=0A@@=20-85,13=20+85,13=20@@=20copy=20=
(values=20(1),=20(2))=20TO=20stdout=20with=20(format=20json);=0A=20=
{"column1":2}=0A=20copy=20(select=201=20union=20all=20select=202)=20to=20=
stdout=20with=20(format=20json,=20force_array=20true);=0A=20[=0A-=20=
{"?column?":1}=0A-,{"?column?":2}=0A+=20{"?column?":1},=0A+=20=
{"?column?":2}=0A=20]=0A=20copy=20(values=20(1),=20(2))=20TO=20stdout=20=
with=20(format=20json,=20force_array=20true);=0A=20[=0A-=20{"column1":1}=0A=
-,{"column1":2}=0A+=20{"column1":1},=0A+=20{"column1":2}=0A=20]=0A=20=
copy=20copytest=20to=20stdout=20json;=0A=20=
{"style":"DOS","test":"abc\r\ndef","filler":1}=0A@@=20-150,17=20+150,17=20=
@@=20ERROR:=20=20COPY=20FORCE_ARRAY=20can=20only=20be=20used=20with=20=
JSON=20mode=0A=20--=20force_array=20variants=0A=20copy=20copytest=20to=20=
stdout=20(format=20json,=20force_array);=0A=20[=0A-=20=
{"style":"DOS","test":"abc\r\ndef","filler":1}=0A=
-,{"style":"Unix","test":"abc\ndef","filler":2}=0A=
-,{"style":"Mac","test":"abc\rdef","filler":3}=0A=
-,{"style":"esc\\ape","test":"a\\r\\\r\\\n\\nb","filler":4}=0A+=20=
{"style":"DOS","test":"abc\r\ndef","filler":1},=0A+=20=
{"style":"Unix","test":"abc\ndef","filler":2},=0A+=20=
{"style":"Mac","test":"abc\rdef","filler":3},=0A+=20=
{"style":"esc\\ape","test":"a\\r\\\r\\\n\\nb","filler":4}=0A=20]=0A=20=
copy=20copytest(style,=20test)=20to=20stdout=20(format=20json,=20=
force_array=20true);=0A=20[=0A-=20{"style":"DOS","test":"abc\r\ndef"}=0A=
-,{"style":"Unix","test":"abc\ndef"}=0A=
-,{"style":"Mac","test":"abc\rdef"}=0A=
-,{"style":"esc\\ape","test":"a\\r\\\r\\\n\\nb"}=0A+=20=
{"style":"DOS","test":"abc\r\ndef"},=0A+=20=
{"style":"Unix","test":"abc\ndef"},=0A+=20=
{"style":"Mac","test":"abc\rdef"},=0A+=20=
{"style":"esc\\ape","test":"a\\r\\\r\\\n\\nb"}=0A=20]=0A=20copy=20=
copytest=20to=20stdout=20(format=20json,=20force_array=20false);=0A=20=
{"style":"DOS","test":"abc\r\ndef","filler":1}=0A--=20=0A2.50.1=20(Apple=20=
Git-155)=0A=0A=

--Apple-Mail=_0B011C42-9154-42CE-9943-ECBFA51C3D51--