Horton

COPY WHERE clause generated/system column reference

jian he <jian.universality@gmail.com>

View thread View on postgr.es
From: jian he <jian.universality@gmail.com>
To: PostgreSQL-development <pgsql-hackers@postgresql.org>
Date: 2025-10-27T08:20:37Z
Lists: pgsql-hackers

Attachments

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

Commits

  1. Disallow generated columns in COPY WHERE clause

  2. Tighten check for generated column in partition key expression