Subject: Bug: COPY FORMAT JSON includes generated columns unlike text/CSV

From: SATYANARAYANA NARLAPURAM <[email-masked]>
Date: Mon, 13 Apr 2026 02:21:28 -0700

Hi hackers,

COPY TO with FORMAT json includes generated columns in the output,
while text and CSV formats correctly exclude them.  Virtual generated
columns appear as null (since they aren't materialized), and stored
generated columns appear with their values  Attached a patch to address
this.

Repro:

    CREATE TABLE t (id int, a int,
        s int GENERATED ALWAYS AS (a * 10) STORED,
        v int GENERATED ALWAYS AS (a * 100) VIRTUAL);
    INSERT INTO t (id, a) VALUES (1, 5);

    COPY t TO STDOUT;
    COPY t TO STDOUT WITH (FORMAT csv, HEADER);
    COPY t TO STDOUT WITH (FORMAT json);
CREATE TABLE
INSERT 0 1
1       5
id,a
1,5
{"id":1,"a":5,"s":50,"v":null}

After the fix:
COPY t TO STDOUT WITH (FORMAT json);
{"id":1,"a":5}

Thanks,
Satya

---

From: jian he <[email-masked]>
Date: Tue, 14 Apr 2026 22:46:56 +0800

On Mon, Apr 13, 2026 at 5:21 PM SATYANARAYANA NARLAPURAM
<[email-masked]> wrote:
> generated columns appear with their values  Attached a patch to address this.
>
The patch looks good to me.

It seems there's a ``git apply`` issue applying the V1 patch.
The attached v2 is the same as your v1, it should apply without issue
using `git apply` or `git am`.

--
jian
https://www.enterprisedb.com/
From 413bc70aaee9efe8cf40153bb31c80de69f5a909 Mon Sep 17 00:00:00 2001
From: jian he <[email-masked]>
Date: Mon, 13 Apr 2026 22:48:47 +0800
Subject: [PATCH v2 1/1] COPY FORMAT JSON with generated

Author:  	SATYANARAYANA NARLAPURAM <satyanarlapuram(at)gmail(dot)com>
discussion: https://postgr.es/m/CAHg%2BQDcfpGDoPL3fvfjXRtfn%3Dfny6DdJR6BAy6TpS1Xj2EZfXA%40mail.gmail.com
commitfest entry: https://commitfest.postgresql.org/patch/
---
 src/backend/commands/copyto.c                   | 2 +-
 src/test/regress/expected/generated_stored.out  | 6 ++++++
 src/test/regress/expected/generated_virtual.out | 6 ++++++
 src/test/regress/sql/generated_stored.sql       | 3 +++
 src/test/regress/sql/generated_virtual.sql      | 3 +++
 5 files changed, 19 insertions(+), 1 deletion(-)

diff --git a/src/backend/commands/copyto.c b/src/backend/commands/copyto.c
index f0e0147c665..85d15353647 100644
--- a/src/backend/commands/copyto.c
+++ b/src/backend/commands/copyto.c
@@ -1033,7 +1033,7 @@ BeginCopyTo(ParseState *pstate,
 	{
 		cstate->json_buf = makeStringInfo();
 
-		if (attnamelist != NIL && rel)
+		if (rel && list_length(cstate->attnumlist) < tupDesc->natts)
 		{
 			int			natts = list_length(cstate->attnumlist);
 			TupleDesc	resultDesc;
diff --git a/src/test/regress/expected/generated_stored.out b/src/test/regress/expected/generated_stored.out
index 43cddeac373..4d329c60994 100644
--- a/src/test/regress/expected/generated_stored.out
+++ b/src/test/regress/expected/generated_stored.out
@@ -541,6 +541,12 @@ SELECT * FROM gtest3 ORDER BY a;
  4 | 12
 (4 rows)
 
+-- COPY JSON should exclude generated columns, same as text/CSV
+COPY gtest1 TO stdout WITH (FORMAT json);
+{"a":1}
+{"a":2}
+{"a":3}
+{"a":4}
 -- null values
 CREATE TABLE gtest2 (a int PRIMARY KEY, b int GENERATED ALWAYS AS (NULL) STORED);
 INSERT INTO gtest2 VALUES (1);
diff --git a/src/test/regress/expected/generated_virtual.out b/src/test/regress/expected/generated_virtual.out
index 234061fa1f7..fc41c480d40 100644
--- a/src/test/regress/expected/generated_virtual.out
+++ b/src/test/regress/expected/generated_virtual.out
@@ -535,6 +535,12 @@ SELECT * FROM gtest3 ORDER BY a;
  4 | 12
 (4 rows)
 
+-- COPY JSON should exclude generated columns, same as text/CSV
+COPY gtest1 TO stdout WITH (FORMAT json);
+{"a":1}
+{"a":2}
+{"a":3}
+{"a":4}
 -- null values
 CREATE TABLE gtest2 (a int PRIMARY KEY, b int GENERATED ALWAYS AS (NULL) VIRTUAL);
 INSERT INTO gtest2 VALUES (1);
diff --git a/src/test/regress/sql/generated_stored.sql b/src/test/regress/sql/generated_stored.sql
index 280021d79b7..1064839dcd2 100644
--- a/src/test/regress/sql/generated_stored.sql
+++ b/src/test/regress/sql/generated_stored.sql
@@ -239,6 +239,9 @@ COPY gtest3 (a, b) FROM stdin;
 
 SELECT * FROM gtest3 ORDER BY a;
 
+-- COPY JSON should exclude generated columns, same as text/CSV
+COPY gtest1 TO stdout WITH (FORMAT json);
+
 -- null values
 CREATE TABLE gtest2 (a int PRIMARY KEY, b int GENERATED ALWAYS AS (NULL) STORED);
 INSERT INTO gtest2 VALUES (1);
diff --git a/src/test/regress/sql/generated_virtual.sql b/src/test/regress/sql/generated_virtual.sql
index 4d9ad3c5dca..9b32413e3a9 100644
--- a/src/test/regress/sql/generated_virtual.sql
+++ b/src/test/regress/sql/generated_virtual.sql
@@ -239,6 +239,9 @@ COPY gtest3 (a, b) FROM stdin;
 
 SELECT * FROM gtest3 ORDER BY a;
 
+-- COPY JSON should exclude generated columns, same as text/CSV
+COPY gtest1 TO stdout WITH (FORMAT json);
+
 -- null values
 CREATE TABLE gtest2 (a int PRIMARY KEY, b int GENERATED ALWAYS AS (NULL) VIRTUAL);
 INSERT INTO gtest2 VALUES (1);
-- 
2.34.1

---

From: Andrew Dunstan <[email-masked]>
Date: Wed, 15 Apr 2026 08:36:33 -0400

> using `git apply` or `git am`.

Thanks. pushed.

cheers

andrew

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