From: Chao Li <li.evan.chao@gmail.com>
Content-Type: multipart/mixed;
	boundary="Apple-Mail=_34018A22-4C2A-4FF8-BD50-2F3B1CF699BF"
Mime-Version: 1.0 (Mac OS X Mail 16.0 \(3864.400.21\))
Subject: COPY JSON: use trailing commas in FORCE_ARRAY output
Message-Id: <DFAC4097-2559-4DED-B7D5-EB53B02E9DA3@gmail.com>
Date: Wed, 6 May 2026 14:40:24 +0800
To: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Archived-At: 
 <https://www.postgresql.org/message-id/DFAC4097-2559-4DED-B7D5-EB53B02E9DA3%40gmail.com>
Precedence: bulk


--Apple-Mail=_34018A22-4C2A-4FF8-BD50-2F3B1CF699BF
Content-Transfer-Encoding: quoted-printable
Content-Type: text/plain;
	charset=us-ascii

Hi,

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"}
]
```

I was surprised by the comma placement. It is valid JSON, but it looks =
quite uncommon.

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)
```

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.

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.

See the attached patch for details.

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


--Apple-Mail=_34018A22-4C2A-4FF8-BD50-2F3B1CF699BF
Content-Disposition: attachment;
	filename=v1-0001-COPY-JSON-use-trailing-commas-in-FORCE_ARRAY-outp.patch
Content-Type: application/octet-stream;
	x-unix-mode=0644;
	name="v1-0001-COPY-JSON-use-trailing-commas-in-FORCE_ARRAY-outp.patch"
Content-Transfer-Encoding: quoted-printable

=46rom=206d6b9fcd3144d10cdf1853902e4054931a7a9ffe=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=
v1]=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:=0A=
Discussion:=20https://postgr.es/m/=0A---=0A=20=
src/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..3dcea96b3a7=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=
CopySendTextLikeEOL(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=09CopySendTextLikeEOL(cstate);=0A=
=20=09=09CopySendChar(cstate,=20']');=0A=20=09=09=
CopySendTextLikeEndOfRow(cstate);=0A=20=09}=0A@@=20-418,7=20+421,11=20@@=20=
CopyToJsonOneRow(CopyToState=20cstate,=20TupleTableSlot=20*slot)=0A=20=09=
if=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=09CopySendTextLikeEOL(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=20row=20needs=20no=20delimiter=20*/=0A=
@@=20-429,7=20+436,10=20@@=20CopyToJsonOneRow(CopyToState=20cstate,=20=
TupleTableSlot=20*slot)=0A=20=0A=20=09CopySendData(cstate,=20=
cstate->json_buf->data,=20cstate->json_buf->len);=0A=20=0A-=09=
CopySendTextLikeEndOfRow(cstate);=0A+=09if=20(cstate->opts.force_array)=0A=
+=09=09CopySendEndOfRow(cstate);=0A+=09else=0A+=09=09=
CopySendTextLikeEndOfRow(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=20for=20text,=20=
CSV,=20and=20json=20formats.=20Sends=20the=0A-=20*=20line=20termination=20=
and=20do=20common=20appropriate=20things=20for=20the=20end=20of=20row.=0A=
+=20*=20Append=20the=20platform-appropriate=20line=20termination=20for=20=
text-like=20output.=0A=20=20*/=0A-static=20inline=20void=0A=
-CopySendTextLikeEndOfRow(CopyToState=20cstate)=0A+static=20void=0A=
+CopySendTextLikeEOL(CopyToState=20cstate)=0A=20{=0A=20=09switch=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=
CopySendTextLikeEOL(cstate);=0A=20=0A=20=09/*=20Now=20take=20the=20=
actions=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=_34018A22-4C2A-4FF8-BD50-2F3B1CF699BF--