COPY WHERE clause generated/system column reference
hi.
CREATE TABLE gtest0 (a int, b int GENERATED ALWAYS AS (a + 1) VIRTUAL);
copy gtest0 from stdin where (b <> 1);
0
\.
ERROR: unexpected virtual generated column reference
CONTEXT: COPY gtest0, line 1: "0"
We need to apply expand_generated_columns_in_expr to the whereClause in DoCopy.
However, handling STORED generated columns appears to be less straightforward.
currently:
ExecQual(cstate->qualexpr, econtext))
happen before
ExecComputeStoredGenerated.
when calling ExecQual, the stored generated column values have not been
populated yet. so we may need ExecComputeStoredGenerated beforehand.
Since ExecComputeStoredGenerated is likely expensive, I added logic to detect
whether the WHERE clause actually have stored generated columns reference before
calling it.
generated column allow tableoid system column reference, COPY WHERE clause also
allow tableoid column reference, should be fine.
please check the attached file:
v1-0001 fix COPY WHERE with system column reference
v1-0002 fix COPY WHERE with generated column reference
Attachments:
v1-0002-fix-COPY-WHERE-clause-generated-column-references.patchtext/x-patch; charset=US-ASCII; name=v1-0002-fix-COPY-WHERE-clause-generated-column-references.patchDownload
From b015777f7db4fe18b2550eecc5f396174cf25c38 Mon Sep 17 00:00:00 2001
From: jian he <jian.universality@gmail.com>
Date: Mon, 27 Oct 2025 16:15:58 +0800
Subject: [PATCH v1 2/2] fix COPY WHERE clause generated column references
discussion: https://postgr.es/m/
---
src/backend/commands/copy.c | 4 +++
src/backend/commands/copyfrom.c | 36 +++++++++++++++++++
.../regress/expected/generated_stored.out | 14 +++++++-
.../regress/expected/generated_virtual.out | 14 +++++++-
src/test/regress/sql/generated_stored.sql | 13 +++++++
src/test/regress/sql/generated_virtual.sql | 13 +++++++
6 files changed, 92 insertions(+), 2 deletions(-)
diff --git a/src/backend/commands/copy.c b/src/backend/commands/copy.c
index a112812d96f..72f78ec647f 100644
--- a/src/backend/commands/copy.c
+++ b/src/backend/commands/copy.c
@@ -33,6 +33,7 @@
#include "parser/parse_collate.h"
#include "parser/parse_expr.h"
#include "parser/parse_relation.h"
+#include "rewrite/rewriteHandler.h"
#include "utils/acl.h"
#include "utils/builtins.h"
#include "utils/lsyscache.h"
@@ -148,6 +149,9 @@ DoCopy(ParseState *pstate, const CopyStmt *stmt,
/* we have to fix its collations too */
assign_expr_collations(pstate, whereClause);
+ /* Expand virtual generated columns in the expr */
+ whereClause = expand_generated_columns_in_expr(whereClause, rel, 1);
+
pull_varattnos(whereClause, 1, &attnums);
k = -1;
diff --git a/src/backend/commands/copyfrom.c b/src/backend/commands/copyfrom.c
index 12781963b4f..3db698f009c 100644
--- a/src/backend/commands/copyfrom.c
+++ b/src/backend/commands/copyfrom.c
@@ -798,6 +798,7 @@ CopyFrom(CopyFromState cstate)
int64 excluded = 0;
bool has_before_insert_row_trig;
bool has_instead_insert_row_trig;
+ bool has_stored_generated = false;
bool leafpart_use_multi_insert = false;
Assert(cstate->rel);
@@ -908,6 +909,37 @@ CopyFrom(CopyFromState cstate)
ti_options |= TABLE_INSERT_FROZEN;
}
+ if (cstate->whereClause)
+ {
+ TupleDesc tupDesc;
+ tupDesc = RelationGetDescr(cstate->rel);
+
+ if (tupDesc->constr && tupDesc->constr->has_generated_stored)
+ {
+ Bitmapset *attnums = NULL;
+ int k = -1;
+
+ pull_varattnos(cstate->whereClause, 1, &attnums);
+ while ((k = bms_next_member(attnums, k)) >= 0)
+ {
+ Form_pg_attribute col;
+ AttrNumber attnum = k + FirstLowInvalidHeapAttributeNumber;
+
+ col = TupleDescAttr(tupDesc, attnum - 1);
+ if (col->attgenerated == ATTRIBUTE_GENERATED_STORED)
+ {
+ /*
+ * The COPY WHERE clause have generated column references,
+ * we need to compute the stored generated column while
+ * evaluating the COPY WHERE clause later.
+ */
+ has_stored_generated = true;
+ break;
+ }
+ }
+ }
+ }
+
/*
* We need a ResultRelInfo so we can use the regular executor's
* index-entry-making machinery. (There used to be a huge amount of code
@@ -1188,6 +1220,10 @@ CopyFrom(CopyFromState cstate)
if (cstate->whereClause)
{
+ if (has_stored_generated)
+ ExecComputeStoredGenerated(resultRelInfo, estate, myslot,
+ CMD_INSERT);
+
econtext->ecxt_scantuple = myslot;
/* Skip items that don't match COPY's WHERE clause */
if (!ExecQual(cstate->qualexpr, econtext))
diff --git a/src/test/regress/expected/generated_stored.out b/src/test/regress/expected/generated_stored.out
index adac2cedfb2..d91989ae4cb 100644
--- a/src/test/regress/expected/generated_stored.out
+++ b/src/test/regress/expected/generated_stored.out
@@ -520,6 +520,7 @@ COPY gtest3 (a, b) TO stdout;
ERROR: column "b" is a generated column
DETAIL: Generated columns cannot be used in COPY.
COPY gtest3 FROM stdin;
+COPY gtest3 FROM stdin WHERE (b <> 15);
COPY gtest3 (a, b) FROM stdin;
ERROR: column "b" is a generated column
DETAIL: Generated columns cannot be used in COPY.
@@ -530,7 +531,8 @@ SELECT * FROM gtest3 ORDER BY a;
2 | 6
3 | 9
4 | 12
-(4 rows)
+ 6 | 18
+(5 rows)
-- null values
CREATE TABLE gtest2 (a int PRIMARY KEY, b int GENERATED ALWAYS AS (NULL) STORED);
@@ -1067,6 +1069,16 @@ SELECT tableoid::regclass, * FROM gtest_parent ORDER BY 1, 2, 3;
gtest_child3 | 09-13-2016 | 1 | 2
(3 rows)
+COPY gtest_parent FROM STDIN WITH DELIMITER ',' WHERE f3 <> 2;
+SELECT tableoid::regclass, * FROM gtest_parent ORDER BY 1, 2, 3;
+ tableoid | f1 | f2 | f3
+--------------+------------+----+----
+ gtest_child | 07-15-2016 | 2 | 4
+ gtest_child | 07-16-2016 | 4 | 8
+ gtest_child2 | 08-15-2016 | 3 | 6
+ gtest_child3 | 09-13-2016 | 1 | 2
+(4 rows)
+
-- we leave these tables around for purposes of testing dump/reload/upgrade
-- generated columns in partition key (not allowed)
CREATE TABLE gtest_part_key (f1 date NOT NULL, f2 bigint, f3 bigint GENERATED ALWAYS AS (f2 * 2) STORED) PARTITION BY RANGE (f3);
diff --git a/src/test/regress/expected/generated_virtual.out b/src/test/regress/expected/generated_virtual.out
index c861bd36c5a..047e0daa68b 100644
--- a/src/test/regress/expected/generated_virtual.out
+++ b/src/test/regress/expected/generated_virtual.out
@@ -514,6 +514,7 @@ COPY gtest3 (a, b) TO stdout;
ERROR: column "b" is a generated column
DETAIL: Generated columns cannot be used in COPY.
COPY gtest3 FROM stdin;
+COPY gtest3 FROM stdin WHERE (b <> 15);
COPY gtest3 (a, b) FROM stdin;
ERROR: column "b" is a generated column
DETAIL: Generated columns cannot be used in COPY.
@@ -524,7 +525,8 @@ SELECT * FROM gtest3 ORDER BY a;
2 | 6
3 | 9
4 | 12
-(4 rows)
+ 6 | 18
+(5 rows)
-- null values
CREATE TABLE gtest2 (a int PRIMARY KEY, b int GENERATED ALWAYS AS (NULL) VIRTUAL);
@@ -1029,6 +1031,16 @@ SELECT tableoid::regclass, * FROM gtest_parent ORDER BY 1, 2, 3;
gtest_child3 | 09-13-2016 | 1 | 2
(3 rows)
+COPY gtest_parent FROM STDIN WITH DELIMITER ',' WHERE f3 <> 2;
+SELECT tableoid::regclass, * FROM gtest_parent ORDER BY 1, 2, 3;
+ tableoid | f1 | f2 | f3
+--------------+------------+----+----
+ gtest_child | 07-15-2016 | 2 | 4
+ gtest_child | 07-16-2016 | 4 | 8
+ gtest_child2 | 08-15-2016 | 3 | 6
+ gtest_child3 | 09-13-2016 | 1 | 2
+(4 rows)
+
-- we leave these tables around for purposes of testing dump/reload/upgrade
-- generated columns in partition key (not allowed)
CREATE TABLE gtest_part_key (f1 date NOT NULL, f2 bigint, f3 bigint GENERATED ALWAYS AS (f2 * 2) VIRTUAL) PARTITION BY RANGE (f3);
diff --git a/src/test/regress/sql/generated_stored.sql b/src/test/regress/sql/generated_stored.sql
index f56fde8d4e5..60770a54d89 100644
--- a/src/test/regress/sql/generated_stored.sql
+++ b/src/test/regress/sql/generated_stored.sql
@@ -231,6 +231,12 @@ COPY gtest3 FROM stdin;
4
\.
+COPY gtest3 FROM stdin WHERE (b <> 15);
+5
+6
+\.
+
+
COPY gtest3 (a, b) FROM stdin;
SELECT * FROM gtest3 ORDER BY a;
@@ -496,6 +502,13 @@ ALTER TABLE gtest_parent ALTER COLUMN f3 SET EXPRESSION AS (f2 * 2);
\d gtest_child2
\d gtest_child3
SELECT tableoid::regclass, * FROM gtest_parent ORDER BY 1, 2, 3;
+
+COPY gtest_parent FROM STDIN WITH DELIMITER ',' WHERE f3 <> 2;
+2016-07-15,1
+2016-07-16,4
+\.
+SELECT tableoid::regclass, * FROM gtest_parent ORDER BY 1, 2, 3;
+
-- we leave these tables around for purposes of testing dump/reload/upgrade
-- generated columns in partition key (not allowed)
diff --git a/src/test/regress/sql/generated_virtual.sql b/src/test/regress/sql/generated_virtual.sql
index adfe88d74ae..4db335de814 100644
--- a/src/test/regress/sql/generated_virtual.sql
+++ b/src/test/regress/sql/generated_virtual.sql
@@ -231,6 +231,12 @@ COPY gtest3 FROM stdin;
4
\.
+COPY gtest3 FROM stdin WHERE (b <> 15);
+5
+6
+\.
+
+
COPY gtest3 (a, b) FROM stdin;
SELECT * FROM gtest3 ORDER BY a;
@@ -539,6 +545,13 @@ ALTER TABLE gtest_parent ALTER COLUMN f3 SET EXPRESSION AS (f2 * 2);
\d gtest_child2
\d gtest_child3
SELECT tableoid::regclass, * FROM gtest_parent ORDER BY 1, 2, 3;
+
+COPY gtest_parent FROM STDIN WITH DELIMITER ',' WHERE f3 <> 2;
+2016-07-15,1
+2016-07-16,4
+\.
+SELECT tableoid::regclass, * FROM gtest_parent ORDER BY 1, 2, 3;
+
-- we leave these tables around for purposes of testing dump/reload/upgrade
-- generated columns in partition key (not allowed)
--
2.34.1
v1-0001-diallow-COPY-WHERE-clause-system-column-references.patchtext/x-patch; charset=US-ASCII; name=v1-0001-diallow-COPY-WHERE-clause-system-column-references.patchDownload
From 8d6b714264a26888da6e94e7e71a596e24fe6597 Mon Sep 17 00:00:00 2001
From: jian he <jian.universality@gmail.com>
Date: Mon, 27 Oct 2025 14:30:24 +0800
Subject: [PATCH v1 1/2] diallow COPY WHERE clause system column references
discussion: https://postgr.es/m/
---
src/backend/commands/copy.c | 17 +++++++++++++++++
src/test/regress/expected/copy2.out | 3 +++
src/test/regress/sql/copy2.sql | 4 ++++
3 files changed, 24 insertions(+)
diff --git a/src/backend/commands/copy.c b/src/backend/commands/copy.c
index 44020d0ae80..a112812d96f 100644
--- a/src/backend/commands/copy.c
+++ b/src/backend/commands/copy.c
@@ -133,6 +133,9 @@ DoCopy(ParseState *pstate, const CopyStmt *stmt,
if (stmt->whereClause)
{
+ Bitmapset *attnums = NULL;
+ int k;
+
/* add nsitem to query namespace */
addNSItemToQuery(pstate, nsitem, false, true, true);
@@ -145,6 +148,20 @@ DoCopy(ParseState *pstate, const CopyStmt *stmt,
/* we have to fix its collations too */
assign_expr_collations(pstate, whereClause);
+ pull_varattnos(whereClause, 1, &attnums);
+
+ k = -1;
+ while ((k = bms_next_member(attnums, k)) >= 0)
+ {
+ AttrNumber attnum = k + FirstLowInvalidHeapAttributeNumber;
+
+ /* Disallow expressions referencing system attributes. */
+ if (attnum <= 0 && attnum != TableOidAttributeNumber)
+ ereport(ERROR,
+ errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("COPY FROM WHERE on system columns is not supported"));
+ }
+
whereClause = eval_const_expressions(NULL, whereClause);
whereClause = (Node *) canonicalize_qual((Expr *) whereClause, false);
diff --git a/src/test/regress/expected/copy2.out b/src/test/regress/expected/copy2.out
index f3fdce23459..cae2c89b95e 100644
--- a/src/test/regress/expected/copy2.out
+++ b/src/test/regress/expected/copy2.out
@@ -159,6 +159,9 @@ CONTEXT: COPY x, line 1: "2002 232 40 50 60 70 80"
COPY x (b, c, d, e) from stdin delimiter ',' null 'x';
COPY x from stdin WITH DELIMITER AS ';' NULL AS '';
COPY x from stdin WITH DELIMITER AS ':' NULL AS E'\\X' ENCODING 'sql_ascii';
+COPY x from stdin WHERE xmin IS NULL; --error
+ERROR: COPY FROM WHERE on system columns is not supported
+COPY x from stdin WHERE tableoid IS NULL; --ok
COPY x TO stdout WHERE a = 1;
ERROR: WHERE clause not allowed with COPY TO
LINE 1: COPY x TO stdout WHERE a = 1;
diff --git a/src/test/regress/sql/copy2.sql b/src/test/regress/sql/copy2.sql
index cef45868db5..383c8204094 100644
--- a/src/test/regress/sql/copy2.sql
+++ b/src/test/regress/sql/copy2.sql
@@ -136,6 +136,10 @@ COPY x from stdin WITH DELIMITER AS ':' NULL AS E'\\X' ENCODING 'sql_ascii';
4008:8:Delimiter:\::\:
\.
+COPY x from stdin WHERE xmin IS NULL; --error
+COPY x from stdin WHERE tableoid IS NULL; --ok
+\.
+
COPY x TO stdout WHERE a = 1;
COPY x from stdin WHERE a = 50004;
50003 24 34 44 54
--
2.34.1
On Mon, 27 Oct 2025 at 13:21, jian he <jian.universality@gmail.com> wrote:
hi.
CREATE TABLE gtest0 (a int, b int GENERATED ALWAYS AS (a + 1) VIRTUAL);
copy gtest0 from stdin where (b <> 1);
0
\.ERROR: unexpected virtual generated column reference
CONTEXT: COPY gtest0, line 1: "0"We need to apply expand_generated_columns_in_expr to the whereClause in DoCopy.
However, handling STORED generated columns appears to be less straightforward.currently:
ExecQual(cstate->qualexpr, econtext))
happen before
ExecComputeStoredGenerated.when calling ExecQual, the stored generated column values have not been
populated yet. so we may need ExecComputeStoredGenerated beforehand.
Since ExecComputeStoredGenerated is likely expensive, I added logic to detect
whether the WHERE clause actually have stored generated columns reference before
calling it.generated column allow tableoid system column reference, COPY WHERE clause also
allow tableoid column reference, should be fine.please check the attached file:
v1-0001 fix COPY WHERE with system column reference
v1-0002 fix COPY WHERE with generated column reference
Hi! Indeed, copying from with generated column in where clause is
broken on HEAD.
I applied your patches, they indeed fix the issue.
Small comment: in 0002:
+ if (has_stored_generated) + ExecComputeStoredGenerated(resultRelInfo, estate, myslot, + CMD_INSERT);
Should we use CMD_UTILITY here? Comment in nodes.h suggests so. Also,
ExecComputeStoredGenerated only check for equality with CMD_UPDATE, so
this is just a cosmetic change.
--
Best regards,
Kirill Reshke
On Tue, Oct 28, 2025 at 2:02 AM Kirill Reshke <reshkekirill@gmail.com> wrote:
Small comment: in 0002:
+ if (has_stored_generated) + ExecComputeStoredGenerated(resultRelInfo, estate, myslot, + CMD_INSERT);Should we use CMD_UTILITY here? Comment in nodes.h suggests so. Also,
ExecComputeStoredGenerated only check for equality with CMD_UPDATE, so
this is just a cosmetic change.
hi.
use CMD_UTILITY will also work as expected.
ExecComputeStoredGenerated expects the command type (cmdtype) to be either
UPDATE or INSERT.
in ExecComputeStoredGenerated, we have:
if (cmdtype == CMD_UPDATE)
else
{
if (resultRelInfo->ri_GeneratedExprsI == NULL)
ExecInitGenerated(resultRelInfo, estate, cmdtype);
/* Early exit is impossible given the prior Assert */
Assert(resultRelInfo->ri_NumGeneratedNeededI > 0);
ri_GeneratedExprs = resultRelInfo->ri_GeneratedExprsI;
}
in struct ResultRelInfo also has comments like:
/*
* Arrays of stored generated columns ExprStates for INSERT/UPDATE/MERGE.
*/
ExprState **ri_GeneratedExprsI;
ExprState **ri_GeneratedExprsU;
I think using CMD_INSERT should be fine. Also, note that below
ExecComputeStoredGenerated uses CMD_INSERT too.
On Mon, Oct 27, 2025 at 1:21 AM jian he <jian.universality@gmail.com> wrote:
hi.
CREATE TABLE gtest0 (a int, b int GENERATED ALWAYS AS (a + 1) VIRTUAL);
copy gtest0 from stdin where (b <> 1);
0
\.ERROR: unexpected virtual generated column reference
CONTEXT: COPY gtest0, line 1: "0"We need to apply expand_generated_columns_in_expr to the whereClause in DoCopy.
However, handling STORED generated columns appears to be less straightforward.currently:
ExecQual(cstate->qualexpr, econtext))
happen before
ExecComputeStoredGenerated.when calling ExecQual, the stored generated column values have not been
populated yet. so we may need ExecComputeStoredGenerated beforehand.
Since ExecComputeStoredGenerated is likely expensive, I added logic to detect
whether the WHERE clause actually have stored generated columns reference before
calling it.
While I agree we can improve the error message in that case as the
message "unexpected virtual generated column reference" isn't helpful
much, I'm not sure that generated column values should be considered
when filtering rows by the WHERE clause. The documentation[1]https://www.postgresql.org/docs/devel/ddl-generated-columns.html#DDL-GENERATED-COLUMNS says:
A stored generated column is computed when it is written (inserted or
updated) and occupies storage as if it were a normal column. A virtual
generated column occupies no storage and is computed when it is read.
The proposed patch (the 0002 patch) allows COPY FROM ... WHERE to
filter rows by checking tuples including generated column values but
it's somewhat odd as it seems not to be the time of reading tuples
from a table.
Also, the patch calls ExecComputeStoredGenerated() before ExecQual(),
which is also before we trigger the BEFORE INSERT trigger. It clearly
violates what the documentation describes[1]https://www.postgresql.org/docs/devel/ddl-generated-columns.html#DDL-GENERATED-COLUMNS:
Generated columns are, conceptually, updated after BEFORE triggers
have run. Therefore, changes made to base columns in a BEFORE trigger
will be reflected in generated columns. But conversely, it is not
allowed to access generated columns in BEFORE triggers.
For example, the tuples passed to a BEFORE INSERT trigger varies
depending on the WHERE clause as follows:
-- preparation
create table t (a int, s int generated always as (a + 10) stored);
create table tt (a int, s int);
create function trig_fn() returns trigger as
$$
begin
insert into tt select NEW.*;
return NEW;
end;
$$ language plpgsql;
create trigger trig before insert on t for each row execute function trig_fn();
-- copy a row without the WHERE clause.
copy t from program 'echo 1';
table tt;
a | s
---+---
1 |
(1 row)
-- copy a row with the where clause
copy t from program 'echo 1' where s > 0;
table tt;
a | s
---+----
1 |
1 | 11
generated column allow tableoid system column reference, COPY WHERE clause also
allow tableoid column reference, should be fine.please check the attached file:
v1-0001 fix COPY WHERE with system column reference
It seems to make sense to disallow users to specify system columns in
the WHERE clause of COPY FROM. But why do we need to have an exception
for tableoid? In the context of COPY FROM, specifying tableoid doesn't
not make sense to me as tuples don't come from any relations. If we
accept tableoid, I think it's better to explain why here.
Regards,
[1]: https://www.postgresql.org/docs/devel/ddl-generated-columns.html#DDL-GENERATED-COLUMNS
--
Masahiko Sawada
Amazon Web Services: https://aws.amazon.com
On Tue, Nov 4, 2025 at 8:27 AM Masahiko Sawada <sawada.mshk@gmail.com> wrote:
The proposed patch (the 0002 patch) allows COPY FROM ... WHERE to
filter rows by checking tuples including generated column values but
it's somewhat odd as it seems not to be the time of reading tuples
from a table.Also, the patch calls ExecComputeStoredGenerated() before ExecQual(),
which is also before we trigger the BEFORE INSERT trigger. It clearly
violates what the documentation describes[1]:For example, the tuples passed to a BEFORE INSERT trigger varies
depending on the WHERE clause as follows:-- preparation
create table t (a int, s int generated always as (a + 10) stored);
create table tt (a int, s int);
create function trig_fn() returns trigger as
$$
begin
insert into tt select NEW.*;
return NEW;
end;
$$ language plpgsql;
create trigger trig before insert on t for each row execute function trig_fn();-- copy a row without the WHERE clause.
copy t from program 'echo 1';
table tt;
a | s
---+---
1 |
(1 row)-- copy a row with the where clause
copy t from program 'echo 1' where s > 0;
table tt;
a | s
---+----
1 |
1 | 11generated column allow tableoid system column reference, COPY WHERE clause also
allow tableoid column reference, should be fine.
for virtual generated column, adding
``whereClause = expand_generated_columns_in_expr(whereClause, rel, 1);``
should be able to solve the problem.
For stored generated columns, we can either
A. document that the stored generated column is not yet computed, it
will be NULL
B. error out if the WHERE clause has a stored generated column.
C. add a temp slot and the computed stored generated column value
stored in the temp slot.
attached v2-0003 using option C to address this problem.
please check the attached file:
v1-0001 fix COPY WHERE with system column referenceIt seems to make sense to disallow users to specify system columns in
the WHERE clause of COPY FROM. But why do we need to have an exception
for tableoid? In the context of COPY FROM, specifying tableoid doesn't
not make sense to me as tuples don't come from any relations. If we
accept tableoid, I think it's better to explain why here.
In function CopyFrom, we have below comment, which indicates
At that time, tableoid was considered in the WHERE clause.
/*
* Constraints and where clause might reference the tableoid column,
* so (re-)initialize tts_tableOid before evaluating them.
*/
myslot->tts_tableOid =
RelationGetRelid(target_resultRelInfo->ri_RelationDesc);
Another possible reason:
tableoid can be referenced in virtual generated column expression.
COPY WHERE clause can be supported for virtual general columns.
CREATE TABLE gtest4 (a int, b oid GENERATED ALWAYS AS ((tableoid)));
COPY gtest4 from stdin where b <> 26420;
COPY gtest4 from stdin where tableoid <> 26420;
we should expect the above two COPY statements behave the same.
please check the attached file:
v2-0001: fix COPY WHERE with system column reference
v2-0002: fix COPY WHERE with virtual generated column reference
v2-0003: fix COPY WHERE with stored generated column reference (experimental)
Attachments:
v2-0003-fix-COPY-WHERE-clause-stored-generated-column-references.patchtext/x-patch; charset=US-ASCII; name=v2-0003-fix-COPY-WHERE-clause-stored-generated-column-references.patchDownload
From 9706a4ebbcfe186b2d6c9fca37187518e4f917c1 Mon Sep 17 00:00:00 2001
From: jian he <jian.universality@gmail.com>
Date: Tue, 4 Nov 2025 19:41:56 +0800
Subject: [PATCH v2 3/3] fix COPY WHERE clause stored generated column
references
discussion: https://postgr.es/m/CACJufxHb8YPQ095R_pYDr77W9XKNaXg5Rzy-WP525mkq+hRM3g@mail.gmail.com
---
src/backend/commands/copyfrom.c | 56 ++++++++++++++++++-
.../regress/expected/generated_stored.out | 14 ++++-
src/test/regress/sql/generated_stored.sql | 13 +++++
3 files changed, 81 insertions(+), 2 deletions(-)
diff --git a/src/backend/commands/copyfrom.c b/src/backend/commands/copyfrom.c
index 12781963b4f..7f1a4728f93 100644
--- a/src/backend/commands/copyfrom.c
+++ b/src/backend/commands/copyfrom.c
@@ -785,6 +785,7 @@ CopyFrom(CopyFromState cstate)
ModifyTableState *mtstate;
ExprContext *econtext;
TupleTableSlot *singleslot = NULL;
+ TupleTableSlot *tmpslot = NULL;
MemoryContext oldcontext = CurrentMemoryContext;
PartitionTupleRouting *proute = NULL;
@@ -798,6 +799,7 @@ CopyFrom(CopyFromState cstate)
int64 excluded = 0;
bool has_before_insert_row_trig;
bool has_instead_insert_row_trig;
+ bool has_generated_stored = false;
bool leafpart_use_multi_insert = false;
Assert(cstate->rel);
@@ -908,6 +910,34 @@ CopyFrom(CopyFromState cstate)
ti_options |= TABLE_INSERT_FROZEN;
}
+ if (cstate->whereClause)
+ {
+ TupleDesc tupDesc = RelationGetDescr(cstate->rel);
+
+ if (tupDesc->constr && tupDesc->constr->has_generated_stored)
+ {
+ int k = -1;
+ Bitmapset *attnums = NULL;
+
+ pull_varattnos(cstate->whereClause, 1, &attnums);
+ while ((k = bms_next_member(attnums, k)) >= 0)
+ {
+ Form_pg_attribute col;
+ AttrNumber attnum = k + FirstLowInvalidHeapAttributeNumber;
+
+ col = TupleDescAttr(tupDesc, attnum - 1);
+ if (col->attgenerated == ATTRIBUTE_GENERATED_STORED)
+ {
+ has_generated_stored = true;
+ break;
+ }
+ }
+ }
+
+ if (has_generated_stored)
+ tmpslot = table_slot_create(cstate->rel, NULL);
+ }
+
/*
* We need a ResultRelInfo so we can use the regular executor's
* index-entry-making machinery. (There used to be a huge amount of code
@@ -1188,7 +1218,20 @@ CopyFrom(CopyFromState cstate)
if (cstate->whereClause)
{
- econtext->ecxt_scantuple = myslot;
+ if (has_generated_stored)
+ {
+ ExecClearTuple(tmpslot);
+
+ ExecCopySlot(tmpslot, myslot);
+
+ ExecComputeStoredGenerated(resultRelInfo, estate, tmpslot,
+ CMD_INSERT);
+
+ econtext->ecxt_scantuple = tmpslot;
+ }
+ else
+ econtext->ecxt_scantuple = myslot;
+
/* Skip items that don't match COPY's WHERE clause */
if (!ExecQual(cstate->qualexpr, econtext))
{
@@ -1489,6 +1532,17 @@ CopyFrom(CopyFromState cstate)
ExecResetTupleTable(estate->es_tupleTable, false);
+ if (has_generated_stored)
+ {
+ ExecClearTuple(tmpslot);
+ tmpslot->tts_ops->release(tmpslot);
+ if (tmpslot->tts_tupleDescriptor)
+ {
+ ReleaseTupleDesc(tmpslot->tts_tupleDescriptor);
+ tmpslot->tts_tupleDescriptor = NULL;
+ }
+ }
+
/* Allow the FDW to shut down */
if (target_resultRelInfo->ri_FdwRoutine != NULL &&
target_resultRelInfo->ri_FdwRoutine->EndForeignInsert != NULL)
diff --git a/src/test/regress/expected/generated_stored.out b/src/test/regress/expected/generated_stored.out
index adac2cedfb2..d91989ae4cb 100644
--- a/src/test/regress/expected/generated_stored.out
+++ b/src/test/regress/expected/generated_stored.out
@@ -520,6 +520,7 @@ COPY gtest3 (a, b) TO stdout;
ERROR: column "b" is a generated column
DETAIL: Generated columns cannot be used in COPY.
COPY gtest3 FROM stdin;
+COPY gtest3 FROM stdin WHERE (b <> 15);
COPY gtest3 (a, b) FROM stdin;
ERROR: column "b" is a generated column
DETAIL: Generated columns cannot be used in COPY.
@@ -530,7 +531,8 @@ SELECT * FROM gtest3 ORDER BY a;
2 | 6
3 | 9
4 | 12
-(4 rows)
+ 6 | 18
+(5 rows)
-- null values
CREATE TABLE gtest2 (a int PRIMARY KEY, b int GENERATED ALWAYS AS (NULL) STORED);
@@ -1067,6 +1069,16 @@ SELECT tableoid::regclass, * FROM gtest_parent ORDER BY 1, 2, 3;
gtest_child3 | 09-13-2016 | 1 | 2
(3 rows)
+COPY gtest_parent FROM STDIN WITH DELIMITER ',' WHERE f3 <> 2;
+SELECT tableoid::regclass, * FROM gtest_parent ORDER BY 1, 2, 3;
+ tableoid | f1 | f2 | f3
+--------------+------------+----+----
+ gtest_child | 07-15-2016 | 2 | 4
+ gtest_child | 07-16-2016 | 4 | 8
+ gtest_child2 | 08-15-2016 | 3 | 6
+ gtest_child3 | 09-13-2016 | 1 | 2
+(4 rows)
+
-- we leave these tables around for purposes of testing dump/reload/upgrade
-- generated columns in partition key (not allowed)
CREATE TABLE gtest_part_key (f1 date NOT NULL, f2 bigint, f3 bigint GENERATED ALWAYS AS (f2 * 2) STORED) PARTITION BY RANGE (f3);
diff --git a/src/test/regress/sql/generated_stored.sql b/src/test/regress/sql/generated_stored.sql
index f56fde8d4e5..60770a54d89 100644
--- a/src/test/regress/sql/generated_stored.sql
+++ b/src/test/regress/sql/generated_stored.sql
@@ -231,6 +231,12 @@ COPY gtest3 FROM stdin;
4
\.
+COPY gtest3 FROM stdin WHERE (b <> 15);
+5
+6
+\.
+
+
COPY gtest3 (a, b) FROM stdin;
SELECT * FROM gtest3 ORDER BY a;
@@ -496,6 +502,13 @@ ALTER TABLE gtest_parent ALTER COLUMN f3 SET EXPRESSION AS (f2 * 2);
\d gtest_child2
\d gtest_child3
SELECT tableoid::regclass, * FROM gtest_parent ORDER BY 1, 2, 3;
+
+COPY gtest_parent FROM STDIN WITH DELIMITER ',' WHERE f3 <> 2;
+2016-07-15,1
+2016-07-16,4
+\.
+SELECT tableoid::regclass, * FROM gtest_parent ORDER BY 1, 2, 3;
+
-- we leave these tables around for purposes of testing dump/reload/upgrade
-- generated columns in partition key (not allowed)
--
2.34.1
v2-0002-fix-COPY-WHERE-clause-virtual-generated-column-references.patchtext/x-patch; charset=US-ASCII; name=v2-0002-fix-COPY-WHERE-clause-virtual-generated-column-references.patchDownload
From fe1e51b9028615bb22c75e7d3c4de4fa9c94abf5 Mon Sep 17 00:00:00 2001
From: jian he <jian.universality@gmail.com>
Date: Tue, 4 Nov 2025 18:16:07 +0800
Subject: [PATCH v2 2/3] fix COPY WHERE clause virtual generated column
references
discussion: https://postgr.es/m/CACJufxHb8YPQ095R_pYDr77W9XKNaXg5Rzy-WP525mkq+hRM3g@mail.gmail.com
---
src/backend/commands/copy.c | 4 ++++
src/test/regress/expected/generated_virtual.out | 14 +++++++++++++-
src/test/regress/sql/generated_virtual.sql | 13 +++++++++++++
3 files changed, 30 insertions(+), 1 deletion(-)
diff --git a/src/backend/commands/copy.c b/src/backend/commands/copy.c
index a112812d96f..e36d8f4fd07 100644
--- a/src/backend/commands/copy.c
+++ b/src/backend/commands/copy.c
@@ -33,6 +33,7 @@
#include "parser/parse_collate.h"
#include "parser/parse_expr.h"
#include "parser/parse_relation.h"
+#include "rewrite/rewriteHandler.h"
#include "utils/acl.h"
#include "utils/builtins.h"
#include "utils/lsyscache.h"
@@ -148,6 +149,9 @@ DoCopy(ParseState *pstate, const CopyStmt *stmt,
/* we have to fix its collations too */
assign_expr_collations(pstate, whereClause);
+ /* Expand virtual generated columns in the whereClause */
+ whereClause = expand_generated_columns_in_expr(whereClause, rel, 1);
+
pull_varattnos(whereClause, 1, &attnums);
k = -1;
diff --git a/src/test/regress/expected/generated_virtual.out b/src/test/regress/expected/generated_virtual.out
index c861bd36c5a..047e0daa68b 100644
--- a/src/test/regress/expected/generated_virtual.out
+++ b/src/test/regress/expected/generated_virtual.out
@@ -514,6 +514,7 @@ COPY gtest3 (a, b) TO stdout;
ERROR: column "b" is a generated column
DETAIL: Generated columns cannot be used in COPY.
COPY gtest3 FROM stdin;
+COPY gtest3 FROM stdin WHERE (b <> 15);
COPY gtest3 (a, b) FROM stdin;
ERROR: column "b" is a generated column
DETAIL: Generated columns cannot be used in COPY.
@@ -524,7 +525,8 @@ SELECT * FROM gtest3 ORDER BY a;
2 | 6
3 | 9
4 | 12
-(4 rows)
+ 6 | 18
+(5 rows)
-- null values
CREATE TABLE gtest2 (a int PRIMARY KEY, b int GENERATED ALWAYS AS (NULL) VIRTUAL);
@@ -1029,6 +1031,16 @@ SELECT tableoid::regclass, * FROM gtest_parent ORDER BY 1, 2, 3;
gtest_child3 | 09-13-2016 | 1 | 2
(3 rows)
+COPY gtest_parent FROM STDIN WITH DELIMITER ',' WHERE f3 <> 2;
+SELECT tableoid::regclass, * FROM gtest_parent ORDER BY 1, 2, 3;
+ tableoid | f1 | f2 | f3
+--------------+------------+----+----
+ gtest_child | 07-15-2016 | 2 | 4
+ gtest_child | 07-16-2016 | 4 | 8
+ gtest_child2 | 08-15-2016 | 3 | 6
+ gtest_child3 | 09-13-2016 | 1 | 2
+(4 rows)
+
-- we leave these tables around for purposes of testing dump/reload/upgrade
-- generated columns in partition key (not allowed)
CREATE TABLE gtest_part_key (f1 date NOT NULL, f2 bigint, f3 bigint GENERATED ALWAYS AS (f2 * 2) VIRTUAL) PARTITION BY RANGE (f3);
diff --git a/src/test/regress/sql/generated_virtual.sql b/src/test/regress/sql/generated_virtual.sql
index adfe88d74ae..4db335de814 100644
--- a/src/test/regress/sql/generated_virtual.sql
+++ b/src/test/regress/sql/generated_virtual.sql
@@ -231,6 +231,12 @@ COPY gtest3 FROM stdin;
4
\.
+COPY gtest3 FROM stdin WHERE (b <> 15);
+5
+6
+\.
+
+
COPY gtest3 (a, b) FROM stdin;
SELECT * FROM gtest3 ORDER BY a;
@@ -539,6 +545,13 @@ ALTER TABLE gtest_parent ALTER COLUMN f3 SET EXPRESSION AS (f2 * 2);
\d gtest_child2
\d gtest_child3
SELECT tableoid::regclass, * FROM gtest_parent ORDER BY 1, 2, 3;
+
+COPY gtest_parent FROM STDIN WITH DELIMITER ',' WHERE f3 <> 2;
+2016-07-15,1
+2016-07-16,4
+\.
+SELECT tableoid::regclass, * FROM gtest_parent ORDER BY 1, 2, 3;
+
-- we leave these tables around for purposes of testing dump/reload/upgrade
-- generated columns in partition key (not allowed)
--
2.34.1
v2-0001-diallow-COPY-WHERE-clause-system-column-references.patchtext/x-patch; charset=US-ASCII; name=v2-0001-diallow-COPY-WHERE-clause-system-column-references.patchDownload
From 60f233f24cedb0f70a6cb92488b69ee1fb0edf18 Mon Sep 17 00:00:00 2001
From: jian he <jian.universality@gmail.com>
Date: Tue, 4 Nov 2025 18:10:06 +0800
Subject: [PATCH v2 1/3] diallow COPY WHERE clause system column references
discussion: https://postgr.es/m/CACJufxHb8YPQ095R_pYDr77W9XKNaXg5Rzy-WP525mkq+hRM3g@mail.gmail.com
---
src/backend/commands/copy.c | 17 +++++++++++++++++
src/test/regress/expected/copy2.out | 3 +++
src/test/regress/sql/copy2.sql | 4 ++++
3 files changed, 24 insertions(+)
diff --git a/src/backend/commands/copy.c b/src/backend/commands/copy.c
index 44020d0ae80..a112812d96f 100644
--- a/src/backend/commands/copy.c
+++ b/src/backend/commands/copy.c
@@ -133,6 +133,9 @@ DoCopy(ParseState *pstate, const CopyStmt *stmt,
if (stmt->whereClause)
{
+ Bitmapset *attnums = NULL;
+ int k;
+
/* add nsitem to query namespace */
addNSItemToQuery(pstate, nsitem, false, true, true);
@@ -145,6 +148,20 @@ DoCopy(ParseState *pstate, const CopyStmt *stmt,
/* we have to fix its collations too */
assign_expr_collations(pstate, whereClause);
+ pull_varattnos(whereClause, 1, &attnums);
+
+ k = -1;
+ while ((k = bms_next_member(attnums, k)) >= 0)
+ {
+ AttrNumber attnum = k + FirstLowInvalidHeapAttributeNumber;
+
+ /* Disallow expressions referencing system attributes. */
+ if (attnum <= 0 && attnum != TableOidAttributeNumber)
+ ereport(ERROR,
+ errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("COPY FROM WHERE on system columns is not supported"));
+ }
+
whereClause = eval_const_expressions(NULL, whereClause);
whereClause = (Node *) canonicalize_qual((Expr *) whereClause, false);
diff --git a/src/test/regress/expected/copy2.out b/src/test/regress/expected/copy2.out
index f3fdce23459..cae2c89b95e 100644
--- a/src/test/regress/expected/copy2.out
+++ b/src/test/regress/expected/copy2.out
@@ -159,6 +159,9 @@ CONTEXT: COPY x, line 1: "2002 232 40 50 60 70 80"
COPY x (b, c, d, e) from stdin delimiter ',' null 'x';
COPY x from stdin WITH DELIMITER AS ';' NULL AS '';
COPY x from stdin WITH DELIMITER AS ':' NULL AS E'\\X' ENCODING 'sql_ascii';
+COPY x from stdin WHERE xmin IS NULL; --error
+ERROR: COPY FROM WHERE on system columns is not supported
+COPY x from stdin WHERE tableoid IS NULL; --ok
COPY x TO stdout WHERE a = 1;
ERROR: WHERE clause not allowed with COPY TO
LINE 1: COPY x TO stdout WHERE a = 1;
diff --git a/src/test/regress/sql/copy2.sql b/src/test/regress/sql/copy2.sql
index cef45868db5..383c8204094 100644
--- a/src/test/regress/sql/copy2.sql
+++ b/src/test/regress/sql/copy2.sql
@@ -136,6 +136,10 @@ COPY x from stdin WITH DELIMITER AS ':' NULL AS E'\\X' ENCODING 'sql_ascii';
4008:8:Delimiter:\::\:
\.
+COPY x from stdin WHERE xmin IS NULL; --error
+COPY x from stdin WHERE tableoid IS NULL; --ok
+\.
+
COPY x TO stdout WHERE a = 1;
COPY x from stdin WHERE a = 50004;
50003 24 34 44 54
--
2.34.1
On 04.11.25 12:43, jian he wrote:
generated column allow tableoid system column reference, COPY WHERE clause also
allow tableoid column reference, should be fine.for virtual generated column, adding
``whereClause = expand_generated_columns_in_expr(whereClause, rel, 1);``should be able to solve the problem.
For stored generated columns, we can either
A. document that the stored generated column is not yet computed, it
will be NULL
B. error out if the WHERE clause has a stored generated column.
C. add a temp slot and the computed stored generated column value
stored in the temp slot.attached v2-0003 using option C to address this problem.
For backpatching, I suggest that we prohibit both stored and virtual
generated column in the COPY WHERE clause. They don't work anyway, so
this doesn't change anything except get a better error message.
We can then consider adding support in future releases, similar to how
we are expanding their use in other contexts in other patches.
Attached is my proposed patch. I kept it similar to the recently
committed fix in commit ba99c9491c4. Note that we also need to consider
whole-row references, as that patch did.
please check the attached file:
v1-0001 fix COPY WHERE with system column referenceIt seems to make sense to disallow users to specify system columns in
the WHERE clause of COPY FROM. But why do we need to have an exception
for tableoid? In the context of COPY FROM, specifying tableoid doesn't
not make sense to me as tuples don't come from any relations. If we
accept tableoid, I think it's better to explain why here.In function CopyFrom, we have below comment, which indicates
At that time, tableoid was considered in the WHERE clause./*
* Constraints and where clause might reference the tableoid column,
* so (re-)initialize tts_tableOid before evaluating them.
*/
myslot->tts_tableOid =
RelationGetRelid(target_resultRelInfo->ri_RelationDesc);
I think this doesn't actually work correctly. I started a separate
thread about this:
/messages/by-id/30c39ee8-bb11-4b8f-9697-45f7e018a8d3@eisentraut.org
Until that is solved, I think we don't need to do anything about system
columns. System columns other than tableoid are already rejected. Once
we know what, if anything, to do about tableoid, we can implement a more
complete check.
Attachments:
0001-Disallow-generated-columns-in-COPY-WHERE-clause.patchtext/plain; charset=UTF-8; name=0001-Disallow-generated-columns-in-COPY-WHERE-clause.patchDownload
From 60070278cf52b286e6b0275dee05565f24773bc6 Mon Sep 17 00:00:00 2001
From: Peter Eisentraut <peter@eisentraut.org>
Date: Wed, 5 Nov 2025 12:29:28 +0100
Subject: [PATCH] Disallow generated columns in COPY WHERE clause
Stored generated columns are not yet computed when the filtering
happens, so we need to prohibit them to avoid incorrect behavior.
Virtual generated columns currently error out ("unexpected virtual
generated column reference"). They could probably work if we expand
them in the right place, but for now let's keep them consistent with
the stored variant. This doesn't change the behavior, it only gives a
nicer error message.
Co-authored-by: jian he <jian.universality@gmail.com>
Reviewed-by: Kirill Reshke <reshkekirill@gmail.com>
Reviewed-by: Masahiko Sawada <sawada.mshk@gmail.com>
Discussion: https://www.postgresql.org/message-id/flat/CACJufxHb8YPQ095R_pYDr77W9XKNaXg5Rzy-WP525mkq+hRM3g@mail.gmail.com
---
src/backend/commands/copy.c | 39 +++++++++++++++++++
.../regress/expected/generated_stored.out | 6 +++
.../regress/expected/generated_virtual.out | 6 +++
src/test/regress/sql/copy2.sql | 1 -
src/test/regress/sql/generated_stored.sql | 4 ++
src/test/regress/sql/generated_virtual.sql | 4 ++
6 files changed, 59 insertions(+), 1 deletion(-)
diff --git a/src/backend/commands/copy.c b/src/backend/commands/copy.c
index 74ae42b19a7..1a25d36e34a 100644
--- a/src/backend/commands/copy.c
+++ b/src/backend/commands/copy.c
@@ -133,6 +133,9 @@ DoCopy(ParseState *pstate, const CopyStmt *stmt,
if (stmt->whereClause)
{
+ Bitmapset *expr_attrs = NULL;
+ int i;
+
/* add nsitem to query namespace */
addNSItemToQuery(pstate, nsitem, false, true, true);
@@ -145,6 +148,42 @@ DoCopy(ParseState *pstate, const CopyStmt *stmt,
/* we have to fix its collations too */
assign_expr_collations(pstate, whereClause);
+ /*
+ * Examine all the columns in the WHERE clause expression. When
+ * the whole-row reference is present, examine all the columns of
+ * the table.
+ */
+ pull_varattnos(whereClause, 1, &expr_attrs);
+ if (bms_is_member(0 - FirstLowInvalidHeapAttributeNumber, expr_attrs))
+ {
+ expr_attrs = bms_add_range(expr_attrs,
+ 1 - FirstLowInvalidHeapAttributeNumber,
+ RelationGetNumberOfAttributes(rel) - FirstLowInvalidHeapAttributeNumber);
+ expr_attrs = bms_del_member(expr_attrs, 0 - FirstLowInvalidHeapAttributeNumber);
+ }
+
+ i = -1;
+ while ((i = bms_next_member(expr_attrs, i)) >= 0)
+ {
+ AttrNumber attno = i + FirstLowInvalidHeapAttributeNumber;
+
+ Assert(attno != 0);
+
+ /*
+ * Prohibit generated columns in the WHERE clause. Stored
+ * generated columns are not yet computed when the filtering
+ * happens. Virtual generated columns could probably work (we
+ * would need to expand them somewhere around here), but for
+ * now we keep them consistent with the stored variant.
+ */
+ if (TupleDescAttr(RelationGetDescr(rel), attno - 1)->attgenerated)
+ ereport(ERROR,
+ errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("generated columns are not supported in COPY FROM WHERE conditions"),
+ errdetail("Column \"%s\" is a generated column.",
+ get_attname(RelationGetRelid(rel), attno, false)));
+ }
+
whereClause = eval_const_expressions(NULL, whereClause);
whereClause = (Node *) canonicalize_qual((Expr *) whereClause, false);
diff --git a/src/test/regress/expected/generated_stored.out b/src/test/regress/expected/generated_stored.out
index b3710a49de6..8b7a71d8f0c 100644
--- a/src/test/regress/expected/generated_stored.out
+++ b/src/test/regress/expected/generated_stored.out
@@ -502,6 +502,12 @@ COPY gtest1 FROM stdin;
COPY gtest1 (a, b) FROM stdin;
ERROR: column "b" is a generated column
DETAIL: Generated columns cannot be used in COPY.
+COPY gtest1 FROM stdin WHERE b <> 10;
+ERROR: generated columns are not supported in COPY FROM WHERE conditions
+DETAIL: Column "b" is a generated column.
+COPY gtest1 FROM stdin WHERE gtest1 IS NULL;
+ERROR: generated columns are not supported in COPY FROM WHERE conditions
+DETAIL: Column "b" is a generated column.
SELECT * FROM gtest1 ORDER BY a;
a | b
---+---
diff --git a/src/test/regress/expected/generated_virtual.out b/src/test/regress/expected/generated_virtual.out
index c5a993cbd8d..b7da03ce7ea 100644
--- a/src/test/regress/expected/generated_virtual.out
+++ b/src/test/regress/expected/generated_virtual.out
@@ -496,6 +496,12 @@ COPY gtest1 FROM stdin;
COPY gtest1 (a, b) FROM stdin;
ERROR: column "b" is a generated column
DETAIL: Generated columns cannot be used in COPY.
+COPY gtest1 FROM stdin WHERE b <> 10;
+ERROR: generated columns are not supported in COPY FROM WHERE conditions
+DETAIL: Column "b" is a generated column.
+COPY gtest1 FROM stdin WHERE gtest1 IS NULL;
+ERROR: generated columns are not supported in COPY FROM WHERE conditions
+DETAIL: Column "b" is a generated column.
SELECT * FROM gtest1 ORDER BY a;
a | b
---+---
diff --git a/src/test/regress/sql/copy2.sql b/src/test/regress/sql/copy2.sql
index 45273557ce0..f33e3ca865f 100644
--- a/src/test/regress/sql/copy2.sql
+++ b/src/test/regress/sql/copy2.sql
@@ -158,7 +158,6 @@ CREATE TRIGGER trg_x_before BEFORE INSERT ON x
COPY x from stdin WHERE a = row_number() over(b);
-
-- check results of copy in
SELECT * FROM x;
diff --git a/src/test/regress/sql/generated_stored.sql b/src/test/regress/sql/generated_stored.sql
index 99ea0105685..2001a47bcc6 100644
--- a/src/test/regress/sql/generated_stored.sql
+++ b/src/test/regress/sql/generated_stored.sql
@@ -217,6 +217,10 @@ CREATE TABLE gtest3a (a text, b text GENERATED ALWAYS AS (a || '+' || a) STORED)
COPY gtest1 (a, b) FROM stdin;
+COPY gtest1 FROM stdin WHERE b <> 10;
+
+COPY gtest1 FROM stdin WHERE gtest1 IS NULL;
+
SELECT * FROM gtest1 ORDER BY a;
TRUNCATE gtest3;
diff --git a/src/test/regress/sql/generated_virtual.sql b/src/test/regress/sql/generated_virtual.sql
index 6683538ac38..81a98995d89 100644
--- a/src/test/regress/sql/generated_virtual.sql
+++ b/src/test/regress/sql/generated_virtual.sql
@@ -217,6 +217,10 @@ CREATE TABLE gtest3a (a text, b text GENERATED ALWAYS AS (a || '+' || a) VIRTUAL
COPY gtest1 (a, b) FROM stdin;
+COPY gtest1 FROM stdin WHERE b <> 10;
+
+COPY gtest1 FROM stdin WHERE gtest1 IS NULL;
+
SELECT * FROM gtest1 ORDER BY a;
TRUNCATE gtest3;
--
2.51.0
On Wed, Nov 5, 2025 at 3:43 AM Peter Eisentraut <peter@eisentraut.org> wrote:
On 04.11.25 12:43, jian he wrote:
generated column allow tableoid system column reference, COPY WHERE clause also
allow tableoid column reference, should be fine.for virtual generated column, adding
``whereClause = expand_generated_columns_in_expr(whereClause, rel, 1);``should be able to solve the problem.
For stored generated columns, we can either
A. document that the stored generated column is not yet computed, it
will be NULL
B. error out if the WHERE clause has a stored generated column.
C. add a temp slot and the computed stored generated column value
stored in the temp slot.attached v2-0003 using option C to address this problem.
For backpatching, I suggest that we prohibit both stored and virtual
generated column in the COPY WHERE clause. They don't work anyway, so
this doesn't change anything except get a better error message.
+1
We can then consider adding support in future releases, similar to how
we are expanding their use in other contexts in other patches.Attached is my proposed patch. I kept it similar to the recently
committed fix in commit ba99c9491c4. Note that we also need to consider
whole-row references, as that patch did.
Here are some minor comments for the proposed patch:
+ ereport(ERROR,
+ errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("generated columns are not
supported in COPY FROM WHERE conditions"),
+ errdetail("Column \"%s\" is a generated column.",
+
get_attname(RelationGetRelid(rel), attno, false)));How about using ERRCODE_INVALID_COLUMN_REFERENCE instead? It's more
consistent with other places where we check the column references.
---
--- a/src/test/regress/sql/copy2.sql
+++ b/src/test/regress/sql/copy2.sql
@@ -161,7 +161,6 @@ COPY x from stdin WHERE a IN (generate_series(1,5));COPY x from stdin WHERE a = row_number() over(b);
-
-- check results of copy in
SELECT * FROM x;
Unnecessary line removal.
The rest looks good to me.
please check the attached file:
v1-0001 fix COPY WHERE with system column referenceIt seems to make sense to disallow users to specify system columns in
the WHERE clause of COPY FROM. But why do we need to have an exception
for tableoid? In the context of COPY FROM, specifying tableoid doesn't
not make sense to me as tuples don't come from any relations. If we
accept tableoid, I think it's better to explain why here.In function CopyFrom, we have below comment, which indicates
At that time, tableoid was considered in the WHERE clause./*
* Constraints and where clause might reference the tableoid column,
* so (re-)initialize tts_tableOid before evaluating them.
*/
myslot->tts_tableOid =
RelationGetRelid(target_resultRelInfo->ri_RelationDesc);I think this doesn't actually work correctly. I started a separate
thread about this:/messages/by-id/30c39ee8-bb11-4b8f-9697-45f7e018a8d3@eisentraut.org
Until that is solved, I think we don't need to do anything about system
columns. System columns other than tableoid are already rejected. Once
we know what, if anything, to do about tableoid, we can implement a more
complete check.
Agreed.
Regards,
--
Masahiko Sawada
Amazon Web Services: https://aws.amazon.com
On 05.11.25 19:19, Masahiko Sawada wrote:
Attached is my proposed patch. I kept it similar to the recently
committed fix in commit ba99c9491c4. Note that we also need to consider
whole-row references, as that patch did.Here are some minor comments for the proposed patch:
+ ereport(ERROR, + errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("generated columns are not supported in COPY FROM WHERE conditions"), + errdetail("Column \"%s\" is a generated column.", + get_attname(RelationGetRelid(rel), attno, false)));How about using ERRCODE_INVALID_COLUMN_REFERENCE instead? It's more
consistent with other places where we check the column references.--- --- a/src/test/regress/sql/copy2.sql +++ b/src/test/regress/sql/copy2.sql @@ -161,7 +161,6 @@ COPY x from stdin WHERE a IN (generate_series(1,5));COPY x from stdin WHERE a = row_number() over(b);
-
-- check results of copy in
SELECT * FROM x;Unnecessary line removal.
The rest looks good to me.
Thanks. I have committed it with these corrections.
On 06.11.25 14:17, Peter Eisentraut wrote:
On 05.11.25 19:19, Masahiko Sawada wrote:
Attached is my proposed patch. I kept it similar to the recently
committed fix in commit ba99c9491c4. Note that we also need to consider
whole-row references, as that patch did.Here are some minor comments for the proposed patch:
+ ereport(ERROR, + errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("generated columns are not supported in COPY FROM WHERE conditions"), + errdetail("Column \"%s\" is a generated column.", + get_attname(RelationGetRelid(rel), attno, false)));How about using ERRCODE_INVALID_COLUMN_REFERENCE instead? It's more
consistent with other places where we check the column references.--- --- a/src/test/regress/sql/copy2.sql +++ b/src/test/regress/sql/copy2.sql @@ -161,7 +161,6 @@ COPY x from stdin WHERE a IN (generate_series(1,5));COPY x from stdin WHERE a = row_number() over(b);
-
-- check results of copy in
SELECT * FROM x;Unnecessary line removal.
The rest looks good to me.
Thanks. I have committed it with these corrections.
The commitfest entry associated with this thread is still open.
The result of the last commit is that generated columns are prohibited
in COPY WHERE expressions.
Earlier in the thread there were proposed patches to allow them to work.
At least for virtual generated columns this was relatively simple, for
stored generated columns it was harder because of the timing relative to
triggers.
After thinking about this some more, I think we should just not do that.
This seems fundamentally wrong, because generated columns are a
property of the target table, but the COPY WHERE clause is meant to
filter the input data. This means the practical use is already quite
questionable. And then as discussed the execution timing relative to
triggers and perhaps other things make this potentially pretty
complicated and semantically dubious.