public inbox for [email protected]
help / color / mirror / Atom feedFrom: SATYANARAYANA NARLAPURAM <[email protected]>
To: PostgreSQL Hackers <[email protected]>
To: Richard Guo <[email protected]>
Subject: Bug: COPY FORMAT JSON includes generated columns unlike text/CSV
Date: Mon, 13 Apr 2026 02:21:28 -0700
Message-ID: <CAHg+QDcfpGDoPL3fvfjXRtfn=fny6DdJR6BAy6TpS1Xj2EZfXA@mail.gmail.com> (raw)
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
Attachments:
[application/octet-stream] v1-00001-copy-json-exclude-generated-columns.patch (3.0K, 3-v1-00001-copy-json-exclude-generated-columns.patch)
download | inline diff:
diff --git a/src/backend/commands/copyto.c b/src/backend/commands/copyto.c
index f0e0147c..e6e9c82d 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;
@@ -1041,6 +1041,13 @@ BeginCopyTo(ParseState *pstate,
/*
* Build a TupleDesc describing only the selected columns so that
* composite_to_json() emits the right column names and types.
*/
resultDesc = CreateTemplateTupleDesc(natts);
diff --git a/src/test/regress/expected/generated_stored.out b/src/test/regress/expected/generated_stored.out
index 43cddeac..4d329c60 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 234061fa..fc41c480 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 280021d7..1064839d 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 4d9ad3c5..9b32413e 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);
view thread (3+ 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], [email protected]
Subject: Re: Bug: COPY FORMAT JSON includes generated columns unlike text/CSV
In-Reply-To: <CAHg+QDcfpGDoPL3fvfjXRtfn=fny6DdJR6BAy6TpS1Xj2EZfXA@mail.gmail.com>
* 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