Skip to content

428C9 — Generated-always column assignment

PostgreSQL SQLSTATE 428C9: Generated-always column assignment (generated_always), source-backed diagnosis and recovery guidance.

428C9 — Generated-always column assignment

At a glance

428C9 (generated_always) is a rewrite-stage ERROR when an INSERT or UPDATE supplies a value where PostgreSQL must generate it or receive DEFAULT. Identity columns and generated columns follow different assignment rules, so the operation and column metadata matter.

Field Value
SQLSTATE 428C9
Condition generated_always
Status active
Known present by 10.0
Locked snapshots 10.23, 11.22, 12.22, 13.23, 14.24, 15.19, 16.15, 17.11, 18.6, 19beta3
Macros ERRCODE_GENERATED_ALWAYS
Aliases —

Meaning

The rewriter computes apply_default for an omitted INSERT target or an explicit DEFAULT. For an identity column marked GENERATED ALWAYS, a non-default INSERT is rejected unless OVERRIDING SYSTEM VALUE is present; OVERRIDING USER VALUE instead forces the default. An identity column marked BY DEFAULT accepts a supplied value, but OVERRIDING USER VALUE still forces generation. Generated columns take only DEFAULT, regardless of an OVERRIDING clause. The INSERT branches identify the column in DETAIL, and the identity-always branch adds HINT Use OVERRIDING SYSTEM VALUE to override.

For UPDATE, the guard covers identity GENERATED ALWAYS and every generated column when a non-default target entry is present; UPDATE has no OVERRIDING escape. After the guard, a virtual generated column gets a null target entry and a stored generated column is filled by the executor. This storage behavior is part of the 18.6 source path and does not turn the catalogue’s 10.0 SQLSTATE lower bound into a claim that every generated-column feature existed in 10.0.

Diagnosis

Inspect pg_attribute.attidentity (a for ALWAYS, d for BY DEFAULT) and attgenerated (s for stored, v for virtual), then record whether the command is INSERT or UPDATE and whether the target entry is omitted, DEFAULT, or a value. For an INSERT from VALUES, confirm whether the offending row item is actually DEFAULT; the rewriter has a separate all-default check. Read the primary, DETAIL, and HINT together: identity-always and generated-column INSERTs share a primary template but have different DETAIL/HINT contracts.

Response

For identity GENERATED ALWAYS, omit the value, use DEFAULT, or use OVERRIDING SYSTEM VALUE only when the application deliberately owns the identity value. For identity BY DEFAULT, leave the value in place when that is the contract; use OVERRIDING USER VALUE when the server must generate it. Remove assignments to generated columns and do not try to bypass the guard with a cast. If the ERROR occurred inside an explicit transaction, roll back to a suitable savepoint or roll back the transaction before issuing the corrected statement; this page has no runtime observation of that recovery.

Messages

The selected 18.6 groups are explicit ERRORs:

Operation Primary DETAIL HINT
identity GENERATED ALWAYS INSERT cannot insert a non-DEFAULT value into column "%s" Column "%s" is an identity column defined as GENERATED ALWAYS. Use OVERRIDING SYSTEM VALUE to override.
generated-column INSERT cannot insert a non-DEFAULT value into column "%s" Column "%s" is a generated column. none
identity GENERATED ALWAYS UPDATE column "%s" can only be updated to DEFAULT Column "%s" is an identity column defined as GENERATED ALWAYS. none
generated-column UPDATE column "%s" can only be updated to DEFAULT Column "%s" is a generated column. none

The fixed source also distinguishes virtual generated storage (null target entry) from stored generated storage (executor fill). Each %s is dynamic; no concrete placeholder value or runtime result is claimed.

Versions

The catalogue records the condition by 10.0 and locks snapshots through 18.6 and 19beta3. That is a definition lower bound, not a claim that identity, stored-generated, and virtual-generated features all share that introduction point. The selected mechanism is PostgreSQL 18.6 source; runtime was not run.

Sources

  • src.errcodes.428C9.18.6 — src/backend/utils/errcodes.txt line 355, fixed at 724edf9bde9d356724ad384a2e196edc3c9f80f7; SHA-256 6e8de346643ba84aa3c9c6a73360acfc7b2dfb89162c06c08ce9bf5bcd5bbcba (source).

  • src.call.428C9.353c4cc772b5ae9adad09c67 — src/backend/rewrite/rewriteHandler.c lines 942-948, fixed at 724edf9bde9d356724ad384a2e196edc3c9f80f7; SHA-256 5a34bf31f424abbf46cd926da1fefcd9360c4d527a87d8a0ac9f653d67c2206a (source).

  • src.call.428C9.47aaa60ecb1566d462ffaa86 — src/backend/rewrite/rewriteHandler.c lines 981-986, fixed at 724edf9bde9d356724ad384a2e196edc3c9f80f7; SHA-256 5a34bf31f424abbf46cd926da1fefcd9360c4d527a87d8a0ac9f653d67c2206a (source).

  • src.call.428C9.8585d423db2446a6222cbf57 — src/backend/rewrite/rewriteHandler.c lines 1008-1013, fixed at 724edf9bde9d356724ad384a2e196edc3c9f80f7; SHA-256 5a34bf31f424abbf46cd926da1fefcd9360c4d527a87d8a0ac9f653d67c2206a (source).

  • src.call.428C9.generated-update — src/backend/rewrite/rewriteHandler.c lines 1015-1030, fixed at 724edf9bde9d356724ad384a2e196edc3c9f80f7; SHA-256 5a34bf31f424abbf46cd926da1fefcd9360c4d527a87d8a0ac9f653d67c2206a (source).

  • src.calls.REL_18_6.428C9 — resolved core call groups; SHA-256 9ee8a0e81d8f0825c5c1ae45583439859a26e602bdd4ce2f2a62aa278867ccbf.

  • authored evidence — source claims, message roles, and runtime boundary.