From fe1e51b9028615bb22c75e7d3c4de4fa9c94abf5 Mon Sep 17 00:00:00 2001 From: jian he 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