PG18 VIRTUAL generated columns silently rewritten as DEFAULT in plan, producing invalid DDL

[kinghuang]

Summary

PostgreSQL 18 VIRTUAL generated columns in the desired-state file are silently rewritten into DEFAULT expressions in the plan. Since a DEFAULT cannot reference other columns, the generated DDL is invalid and fails at apply time with ERROR: cannot use column reference in DEFAULT expression (SQLSTATE 0A000).

STORED generated columns round-trip correctly — only VIRTUAL (new in PG18) is affected.

Environment

• pgschema: v1.11.1 (1.11.1@7efe9b83 darwin/arm64) — also reproduced on v1.11.0
• Target: PostgreSQL 18.4 (postgres:18 Docker image)
• Plan DB: external, same instance

Reproduction

Desired state (vt.sql):

CREATE TABLE vt (
    slug text NOT NULL,
    identifier text GENERATED ALWAYS AS ('urn:sdp:catalog:' || slug) VIRTUAL
);

Target: empty public schema on PostgreSQL 18.4.

pgschema plan --host localhost --port 15498 --db postgres --user postgres \
  --schema public --file vt.sql \
  --plan-host localhost --plan-port 15498 --plan-db plandb --plan-user postgres \
  --output-sql stdout

Observed plan output

CREATE TABLE IF NOT EXISTS vt (
    slug text NOT NULL,
    identifier text DEFAULT ('urn:sdp:catalog:' || slug)
);

The GENERATED ALWAYS AS (…) VIRTUAL clause has been rewritten into a DEFAULT expression.

Apply fails

Executing group 1/1...
  Executing 1 statements in implicit transaction
Error: failed to execute concatenated statements in group 1: ERROR: cannot use column reference in DEFAULT expression (SQLSTATE 0A000)

PostgreSQL rejects the DDL because a DEFAULT expression may not reference other columns:

ERROR:  cannot use column reference in DEFAULT expression
LINE 3:     identifier text DEFAULT ('urn:sdp:catalog:' || slug)
                                                           ^

Control: STORED works

The same desired state with STORED instead of VIRTUAL plans correctly:

CREATE TABLE IF NOT EXISTS vt (
    slug text NOT NULL,
    identifier text GENERATED ALWAYS AS (('urn:sdp:catalog:'::text || slug)) STORED
);

Expected behavior

Either of:

1. Faithful support for VIRTUAL generated columns on PG18+ targets (emit GENERATED ALWAYS AS (…) VIRTUAL), or
2. An explicit unsupported-syntax error when the desired state contains VIRTUAL.

Silently rewriting the column into a different (and invalid) construct is the worst outcome — the plan looks plausible and the corruption only surfaces at apply time. Note that in PG18, GENERATED ALWAYS AS (…) with no keyword defaults to VIRTUAL, so this is also hit by users who omit the keyword entirely.

Possibly related

• Issue with generated columns #183 reported a similar GENERATED … STORED → DEFAULT rewrite in dump output (fixed; PR chore: generated_column test case #184). This looks like the same class of parsing gap, but for the VIRTUAL keyword.
• Support Postgres 18 #186 added PG18 version support; VIRTUAL columns appear to have been missed.