[PATCH] COPY TO FORMAT json: respect column list order

On 2026-06-12 Fr 6:50 PM, Baji Shaik wrote:

Hi,

While testing the new COPY JSON format, I noticed the column list
ordering is ignored when the list mentions every column of the
relation.  CSV and TEXT formats respect the order; JSON does not.

  CREATE TABLE t (a int, b int);
  INSERT INTO t VALUES (1, 2);

  COPY t (b, a) TO STDOUT WITH (FORMAT csv);   -- "2,1"
  COPY t (b, a) TO STDOUT WITH (FORMAT json);  -- {"a":1,"b":2}
                                               -- expected: {"b":2,"a":1}

In BeginCopyTo() (commit 7dadd38cda9), the JSON path builds a custom
TupleDesc only when the column list is shorter than the relation:

  if (rel && list_length(cstate->attnumlist) < tupDesc->natts)

When the user provides a full column list reordered, list_length
equals natts, so the natural TupleDesc is reused.

Fix is to extend the condition to also fire when an explicit column list
was supplied (attnamelist != NIL).

All 245 regression tests pass.

This seems to be a harmless enough change, and if we're going to do it
we should do it now.

OTOH, any application that relies on a particular field ordering in JSON
data is broken by design, IMNSHO.

cheers

andrew

--
Andrew Dunstan
EDB: https://www.enterprisedb.com

This seems to be a harmless enough change, and if we're going to do it
we should do it now.

+1

OTOH, any application that relies on a particular field ordering in JSON
data is broken by design, IMNSHO.

This should be done for the sake of consistent behavior when the COPY
TO command is supplied with an explicit list.

My comments on the patch:

1/
+ *
+ * This fires when the user gave an explicit column list (which may
+ * subset or reorder columns) or when the default list excludes
+ * generated columns.
*/

This comment is not needed.

But I think we should add a comment explaining the condition here.

-        if (rel && list_length(cstate->attnumlist) < tupDesc->natts)
+        if (rel && (attnamelist != NIL ||
+                    list_length(cstate->attnumlist) < tupDesc->natts))
         {

Also maybe simplify this comment

2/
+-- column list that reorders all columns must be honored in the JSON output,
+-- like text/CSV (the keys must appear in the requested order, not the table's
+-- physical order)

Should we just add tests for all the output types?

copy2.sql has a reordering test for text

-- column reordering
COPY rls_t1 (b, a) TO stdout;

but that is just incidental for rls testing.

--
Sami Imseih
Amazon Web Services (AWS)

On Sat, Jun 13, 2026 at 6:07 PM Sami Imseih <samimseih@gmail.com> wrote:

This comment is not needed.

But I think we should add a comment explaining the condition here.

Done, replaced with a short comment above the condition and
dropped the inner block comment.

Should we just add tests for all the output types?

Added. v2 has column-reorder tests for text and csv (before the
JSON section) plus subset and reorder tests for JSON in the JSON
section.

Thanks for the review, Sami. And thanks Andrew for the +1.

v2 attached.

v2 attached.

Thanks! This looks good to me overall. In the attached, I
simplified the code comments a bit and removed the
column subset test. We don't need that as we
dump column subsets for json in other tests.

--
Sami Imseih
Amazon Web Services (AWS)

On Mon, Jun 15, 2026 at 5:55 PM Sami Imseih <samimseih@gmail.com> wrote:

v2 attached.

LGTM. Thank you, Sami.

Thanks,
Baji Shaik.