public inbox for [email protected]
help / color / mirror / Atom feedFrom: Chao Li <[email protected]>
To: Alex Guo <[email protected]>
Cc: PostgreSQL Hackers <[email protected]>
Subject: Re: COPY JSON: use trailing commas in FORCE_ARRAY output
Date: Wed, 6 May 2026 17:25:38 +0800
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>
<[email protected]>
> On May 6, 2026, at 16:46, Alex Guo <[email protected]> wrote:
>
>
> On 5/6/26 2:40 PM, Chao Li wrote:
>> 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/
>>
>>
>>
> Thanks for the patch, I like it as I feel better with placing commas at the end of lines.
>
> I have a small suggestion. The function name CopySendTextLikeEOL reads very similar to the existing CopySendTextLikeEndOfRow. Would it better to rename it to CopySendTextLikeLineTerminator?
>
> Other than that, the patch looks good to me.
>
> 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/
Attachments:
[application/octet-stream] v2-0001-COPY-JSON-use-trailing-commas-in-FORCE_ARRAY-outp.patch (5.4K, 2-v2-0001-COPY-JSON-use-trailing-commas-in-FORCE_ARRAY-outp.patch)
download | inline diff:
From 4f2151348d89f9417d161729b5b717d32804d702 Mon Sep 17 00:00:00 2001
From: "Chao Li (Evan)" <[email protected]>
Date: Wed, 6 May 2026 14:14:03 +0800
Subject: [PATCH v2] 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: Ayush Tiwari <[email protected]>
Reviewed-by: Daniel Gustafsson <[email protected]>
Reviewed-by: Alex Guo <[email protected]>
Discussion: https://postgr.es/m/[email protected]
---
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..e98a15dcd64 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 CopySendTextLikeLineTerminator(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)
+ CopySendTextLikeLineTerminator(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, ',');
+ CopySendTextLikeLineTerminator(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
+CopySendTextLikeLineTerminator(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)
+{
+ CopySendTextLikeLineTerminator(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)
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], [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