public inbox for [email protected]
help / color / mirror / Atom feedFrom: Chao Li <[email protected]>
To: PostgreSQL Hackers <[email protected]>
Subject: COPY JSON: use trailing commas in FORCE_ARRAY output
Date: Wed, 6 May 2026 14:40:24 +0800
Message-ID: <[email protected]> (raw)
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=# 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":"2026-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=# 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/
Attachments:
[application/octet-stream] v1-0001-COPY-JSON-use-trailing-commas-in-FORCE_ARRAY-outp.patch (5.2K, 2-v1-0001-COPY-JSON-use-trailing-commas-in-FORCE_ARRAY-outp.patch)
download | inline diff:
From 6d6b9fcd3144d10cdf1853902e4054931a7a9ffe Mon Sep 17 00:00:00 2001
From: "Chao Li (Evan)" <[email protected]>
Date: Wed, 6 May 2026 14:14:03 +0800
Subject: [PATCH v1] COPY JSON: use trailing commas in FORCE_ARRAY output
Change COPY TO ... FORMAT JSON, FORCE_ARRAY output to place commas at
the end of each array element line, instead of at the beginning of the
next line.
Previously, output looked like this:
```
[
{"id":1}
,{"id":2}
]
```
This is valid JSON, but it is an unusual formatting style and can be
surprising to readers. Make it emit the more conventional form instead:
```
[
{"id":1},
{"id":2}
]
```
Implement this without buffering the whole result by adjusting how JSON
rows are terminated and how the separator is emitted between rows.
Update the regression test output accordingly.
Author: Chao Li <[email protected]>
Reviewed-by:
Discussion: https://postgr.es/m/
---
src/backend/commands/copyto.c | 29 ++++++++++++++++++++++++-----
src/test/regress/expected/copy.out | 24 ++++++++++++------------
2 files changed, 36 insertions(+), 17 deletions(-)
diff --git a/src/backend/commands/copyto.c b/src/backend/commands/copyto.c
index 85d15353647..3dcea96b3a7 100644
--- a/src/backend/commands/copyto.c
+++ b/src/backend/commands/copyto.c
@@ -156,6 +156,7 @@ static void CopySendData(CopyToState cstate, const void *databuf, int datasize);
static void CopySendString(CopyToState cstate, const char *str);
static void CopySendChar(CopyToState cstate, char c);
static void CopySendEndOfRow(CopyToState cstate);
+static void CopySendTextLikeEOL(CopyToState cstate);
static void CopySendTextLikeEndOfRow(CopyToState cstate);
static void CopySendInt32(CopyToState cstate, int32 val);
static void CopySendInt16(CopyToState cstate, int16 val);
@@ -349,6 +350,8 @@ CopyToJsonEnd(CopyToState cstate)
{
if (cstate->opts.force_array)
{
+ if (cstate->json_row_delim_needed)
+ CopySendTextLikeEOL(cstate);
CopySendChar(cstate, ']');
CopySendTextLikeEndOfRow(cstate);
}
@@ -418,7 +421,11 @@ CopyToJsonOneRow(CopyToState cstate, TupleTableSlot *slot)
if (cstate->opts.force_array)
{
if (cstate->json_row_delim_needed)
+ {
CopySendChar(cstate, ',');
+ CopySendTextLikeEOL(cstate);
+ CopySendChar(cstate, ' ');
+ }
else
{
/* first row needs no delimiter */
@@ -429,7 +436,10 @@ CopyToJsonOneRow(CopyToState cstate, TupleTableSlot *slot)
CopySendData(cstate, cstate->json_buf->data, cstate->json_buf->len);
- CopySendTextLikeEndOfRow(cstate);
+ if (cstate->opts.force_array)
+ CopySendEndOfRow(cstate);
+ else
+ CopySendTextLikeEndOfRow(cstate);
}
/*
@@ -641,11 +651,10 @@ CopySendEndOfRow(CopyToState cstate)
}
/*
- * Wrapper function of CopySendEndOfRow for text, CSV, and json formats. Sends the
- * line termination and do common appropriate things for the end of row.
+ * Append the platform-appropriate line termination for text-like output.
*/
-static inline void
-CopySendTextLikeEndOfRow(CopyToState cstate)
+static void
+CopySendTextLikeEOL(CopyToState cstate)
{
switch (cstate->copy_dest)
{
@@ -664,6 +673,16 @@ CopySendTextLikeEndOfRow(CopyToState cstate)
default:
break;
}
+}
+
+/*
+ * Wrapper function of CopySendEndOfRow for text, CSV, and json formats. Sends the
+ * line termination and do common appropriate things for the end of row.
+ */
+static inline void
+CopySendTextLikeEndOfRow(CopyToState cstate)
+{
+ CopySendTextLikeEOL(cstate);
/* Now take the actions related to the end of a row */
CopySendEndOfRow(cstate);
diff --git a/src/test/regress/expected/copy.out b/src/test/regress/expected/copy.out
index 1714faab39c..3da23de8551 100644
--- a/src/test/regress/expected/copy.out
+++ b/src/test/regress/expected/copy.out
@@ -85,13 +85,13 @@ copy (values (1), (2)) TO stdout with (format json);
{"column1":2}
copy (select 1 union all select 2) to stdout with (format json, force_array true);
[
- {"?column?":1}
-,{"?column?":2}
+ {"?column?":1},
+ {"?column?":2}
]
copy (values (1), (2)) TO stdout with (format json, force_array true);
[
- {"column1":1}
-,{"column1":2}
+ {"column1":1},
+ {"column1":2}
]
copy copytest to stdout json;
{"style":"DOS","test":"abc\r\ndef","filler":1}
@@ -150,17 +150,17 @@ ERROR: COPY FORCE_ARRAY can only be used with JSON mode
-- force_array variants
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}
+ {"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}
]
copy copytest(style, test) to stdout (format json, force_array true);
[
- {"style":"DOS","test":"abc\r\ndef"}
-,{"style":"Unix","test":"abc\ndef"}
-,{"style":"Mac","test":"abc\rdef"}
-,{"style":"esc\\ape","test":"a\\r\\\r\\\n\\nb"}
+ {"style":"DOS","test":"abc\r\ndef"},
+ {"style":"Unix","test":"abc\ndef"},
+ {"style":"Mac","test":"abc\rdef"},
+ {"style":"esc\\ape","test":"a\\r\\\r\\\n\\nb"}
]
copy copytest to stdout (format json, force_array false);
{"style":"DOS","test":"abc\r\ndef","filler":1}
--
2.50.1 (Apple Git-155)
view thread (6+ messages) latest in thread
reply
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: [email protected]
Cc: [email protected], [email protected]
Subject: Re: COPY JSON: use trailing commas in FORCE_ARRAY output
In-Reply-To: <[email protected]>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox