public inbox for [email protected]
help / color / mirror / Atom feedBug: COPY FORMAT JSON includes generated columns unlike text/CSV
3+ messages / 3 participants
[nested] [flat]
* Bug: COPY FORMAT JSON includes generated columns unlike text/CSV
@ 2026-04-13 09:21 SATYANARAYANA NARLAPURAM <[email protected]>
0 siblings, 1 reply; 3+ messages in thread
From: SATYANARAYANA NARLAPURAM @ 2026-04-13 09:21 UTC (permalink / raw)
To: PostgreSQL Hackers <[email protected]>; Richard Guo <[email protected]>
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);
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Bug: COPY FORMAT JSON includes generated columns unlike text/CSV
@ 2026-04-14 14:46 jian he <[email protected]>
parent: SATYANARAYANA NARLAPURAM <[email protected]>
0 siblings, 1 reply; 3+ messages in thread
From: jian he @ 2026-04-14 14:46 UTC (permalink / raw)
To: SATYANARAYANA NARLAPURAM <[email protected]>; +Cc: PostgreSQL Hackers <[email protected]>; Richard Guo <[email protected]>
On Mon, Apr 13, 2026 at 5:21 PM SATYANARAYANA NARLAPURAM
<[email protected]> wrote:
>
> 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.
>
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/
Attachments:
[text/x-patch] v2-0001-COPY-FORMAT-JSON-with-generated.patch (3.5K, 2-v2-0001-COPY-FORMAT-JSON-with-generated.patch)
download | inline diff:
From 413bc70aaee9efe8cf40153bb31c80de69f5a909 Mon Sep 17 00:00:00 2001
From: jian he <[email protected]>
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Bug: COPY FORMAT JSON includes generated columns unlike text/CSV
@ 2026-04-15 12:36 Andrew Dunstan <[email protected]>
parent: jian he <[email protected]>
0 siblings, 0 replies; 3+ messages in thread
From: Andrew Dunstan @ 2026-04-15 12:36 UTC (permalink / raw)
To: jian he <[email protected]>; SATYANARAYANA NARLAPURAM <[email protected]>; +Cc: PostgreSQL Hackers <[email protected]>; Richard Guo <[email protected]>
On 2026-04-14 Tu 10:46 AM, jian he wrote:
> On Mon, Apr 13, 2026 at 5:21 PM SATYANARAYANA NARLAPURAM
> <[email protected]> wrote:
>> 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.
>>
> 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`.
>
Thanks. pushed.
cheers
andrew
--
Andrew Dunstan
EDB:https://www.enterprisedb.com
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2026-04-15 12:36 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-04-13 09:21 Bug: COPY FORMAT JSON includes generated columns unlike text/CSV SATYANARAYANA NARLAPURAM <[email protected]>
2026-04-14 14:46 ` jian he <[email protected]>
2026-04-15 12:36 ` Andrew Dunstan <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox