COPY WHERE clause generated/system column reference

From: jian he <jian(dot)universality(at)gmail(dot)com>
To: PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>
Subject: COPY WHERE clause generated/system column reference
Date: 2025-10-27 08:20:37
Message-ID: CACJufxHb8YPQ095R_pYDr77W9XKNaXg5Rzy-WP525mkq+hRM3g@mail.gmail.com
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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

Attachment Content-Type Size
v1-0002-fix-COPY-WHERE-clause-generated-column-references.patch text/x-patch 8.1 KB
v1-0001-diallow-COPY-WHERE-clause-system-column-references.patch text/x-patch 2.8 KB

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message shveta malik 2025-10-27 08:29:41 Re: Logical Replication of sequences
Previous Message Jim Jones 2025-10-27 08:08:17 Re: display hot standby state in psql prompt