↑↓ select↵ open⌫ change scopeOpen full search

PG.CENTER connects PostgreSQL documentation, reference, and ecosystem knowledge. Maintained by Pigsty.

Wiki / SQLSTATE

428C9 — Generated-always column assignment

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.

English manual coverage begins with PostgreSQL 8.1. Source history and runtime observations have their own recorded version boundaries.

Reading PG 18·Select a recorded version

1011121314151617181920
aliases
Unknown
class name
Syntax Error or Access Rule Violation
condition name
generated_always
lang
en
sqlstate
428C9

Definition PG 18

428C9 — Generated-always column assignment

At a glance {#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.

Meaning {#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 {#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 {#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 {#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 {#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.

Related {#related}

Sources {#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.

Compare versions

SQLSTATE evidence and recorded runtime versions

claims · identity.class-and-condition
{
  "claim_id": "identity.class-and-condition",
  "limits": "Identity does not enumerate all source branches or client failures.",
  "method": "Read fixed REL_18_6 errcodes.txt and locked facts.",
  "position": 0,
  "runtime": [],
  "sources": [
    "src.errcodes.428C9.18.6"
  ],
  "statement": "428C9 is the generated_always condition in Class 42."
}
claims · mechanism.selected-source-path
{
  "claim_id": "mechanism.selected-source-path",
  "limits": "Representative confirmed core paths only; not exhaustive historical or extension coverage.",
  "method": "Read complete resolved REL_18_6 report groups and message roles.",
  "position": 1,
  "runtime": [],
  "sources": [
    "src.call.428C9.353c4cc772b5ae9adad09c67",
    "src.call.428C9.47aaa60ecb1566d462ffaa86",
    "src.call.428C9.8585d423db2446a6222cbf57",
    "src.call.428C9.generated-update"
  ],
  "statement": "The fixed rewrite branches compute apply_default for omitted INSERT targets and DEFAULT nodes. GENERATED ALWAYS identity INSERTs reject non-default values unless OVERRIDING SYSTEM VALUE is present; OVERRIDING USER VALUE forces the default, while BY DEFAULT identity accepts a supplied value unless USER VALUE is requested. Generated columns accept only DEFAULT. UPDATE guards reject non-default assignments to GENERATED ALWAYS identity and generated columns; virtual generated columns receive a null target entry and stored generated columns are filled by the executor."
}
claims · versions.catalogue-boundary
{
  "claim_id": "versions.catalogue-boundary",
  "limits": "First observed release is a lower bound, not precise behavioral introduction.",
  "method": "Read locked catalogue facts and definition snapshots.",
  "position": 2,
  "runtime": [],
  "sources": [
    "src.errcodes.428C9.18.6"
  ],
  "statement": "The catalogue records the condition by 10.0 and locks snapshots through 18.6 and 19beta3; this is a definition lower bound, not a shared introduction point for identity, stored-generated, and virtual-generated features. The selected mechanism is fixed 18.6 source and runtime was not run."
}
messages · message.variant-1
{
  "limits": "Placeholders remain dynamic source fields; no concrete runtime value is claimed.",
  "message_id": "message.variant-1",
  "path": "",
  "position": 0,
  "raw": {
    "detail_templates": [
      "Column \"%s\" is an identity column defined as GENERATED ALWAYS."
    ],
    "hint_templates": [
      "Use OVERRIDING SYSTEM VALUE to override."
    ],
    "id": "message.variant-1",
    "limits": "Placeholders remain dynamic source fields; no concrete runtime value is claimed.",
    "primary_template": "cannot insert a non-DEFAULT value into column \"%s\"",
    "roles": [
      {
        "role": "errmsg",
        "template": "cannot insert a non-DEFAULT value into column \"%s\""
      },
      {
        "role": "errdetail",
        "template": "Column \"%s\" is an identity column defined as GENERATED ALWAYS."
      },
      {
        "role": "errhint",
        "template": "Use OVERRIDING SYSTEM VALUE to override."
      }
    ],
    "severity_source": "explicit ERROR",
    "sources": [
      "src.call.428C9.353c4cc772b5ae9adad09c67"
    ],
    "sqlstate": "428C9"
  },
  "severity": "explicit ERROR",
  "sources": [
    "src.call.428C9.353c4cc772b5ae9adad09c67"
  ],
  "templates": [
    {
      "kind": "primary",
      "literal": "cannot insert a non-DEFAULT value into column \" \"",
      "position": 0,
      "role": "",
      "template": "cannot insert a non-DEFAULT value into column \"%s\""
    },
    {
      "kind": "detail",
      "literal": "Column \" \" is an identity column defined as GENERATED ALWAYS.",
      "position": 1,
      "role": "",
      "template": "Column \"%s\" is an identity column defined as GENERATED ALWAYS."
    },
    {
      "kind": "hint",
      "literal": "Use OVERRIDING SYSTEM VALUE to override.",
      "position": 2,
      "role": "",
      "template": "Use OVERRIDING SYSTEM VALUE to override."
    },
    {
      "kind": "primary",
      "literal": "Column \" \" is an identity column defined as GENERATED ALWAYS.",
      "position": 3,
      "role": "errdetail",
      "template": "Column \"%s\" is an identity column defined as GENERATED ALWAYS."
    },
    {
      "kind": "primary",
      "literal": "Use OVERRIDING SYSTEM VALUE to override.",
      "position": 4,
      "role": "errhint",
      "template": "Use OVERRIDING SYSTEM VALUE to override."
    }
  ]
}
messages · message.variant-2
{
  "limits": "Placeholders remain dynamic source fields; no concrete runtime value is claimed.",
  "message_id": "message.variant-2",
  "path": "",
  "position": 1,
  "raw": {
    "detail_templates": [
      "Column \"%s\" is a generated column."
    ],
    "hint_templates": [],
    "id": "message.variant-2",
    "limits": "Placeholders remain dynamic source fields; no concrete runtime value is claimed.",
    "primary_template": "cannot insert a non-DEFAULT value into column \"%s\"",
    "roles": [
      {
        "role": "errmsg",
        "template": "cannot insert a non-DEFAULT value into column \"%s\""
      },
      {
        "role": "errdetail",
        "template": "Column \"%s\" is a generated column."
      }
    ],
    "severity_source": "explicit ERROR",
    "sources": [
      "src.call.428C9.47aaa60ecb1566d462ffaa86"
    ],
    "sqlstate": "428C9"
  },
  "severity": "explicit ERROR",
  "sources": [
    "src.call.428C9.47aaa60ecb1566d462ffaa86"
  ],
  "templates": [
    {
      "kind": "primary",
      "literal": "cannot insert a non-DEFAULT value into column \" \"",
      "position": 0,
      "role": "",
      "template": "cannot insert a non-DEFAULT value into column \"%s\""
    },
    {
      "kind": "detail",
      "literal": "Column \" \" is a generated column.",
      "position": 1,
      "role": "",
      "template": "Column \"%s\" is a generated column."
    },
    {
      "kind": "primary",
      "literal": "Column \" \" is a generated column.",
      "position": 2,
      "role": "errdetail",
      "template": "Column \"%s\" is a generated column."
    }
  ]
}
messages · message.variant-3
{
  "limits": "Placeholders remain dynamic source fields; no concrete runtime value is claimed.",
  "message_id": "message.variant-3",
  "path": "",
  "position": 2,
  "raw": {
    "detail_templates": [
      "Column \"%s\" is an identity column defined as GENERATED ALWAYS."
    ],
    "hint_templates": [],
    "id": "message.variant-3",
    "limits": "Placeholders remain dynamic source fields; no concrete runtime value is claimed.",
    "primary_template": "column \"%s\" can only be updated to DEFAULT",
    "roles": [
      {
        "role": "errmsg",
        "template": "column \"%s\" can only be updated to DEFAULT"
      },
      {
        "role": "errdetail",
        "template": "Column \"%s\" is an identity column defined as GENERATED ALWAYS."
      }
    ],
    "severity_source": "explicit ERROR",
    "sources": [
      "src.call.428C9.8585d423db2446a6222cbf57"
    ],
    "sqlstate": "428C9"
  },
  "severity": "explicit ERROR",
  "sources": [
    "src.call.428C9.8585d423db2446a6222cbf57"
  ],
  "templates": [
    {
      "kind": "primary",
      "literal": "column \" \" can only be updated to DEFAULT",
      "position": 0,
      "role": "",
      "template": "column \"%s\" can only be updated to DEFAULT"
    },
    {
      "kind": "detail",
      "literal": "Column \" \" is an identity column defined as GENERATED ALWAYS.",
      "position": 1,
      "role": "",
      "template": "Column \"%s\" is an identity column defined as GENERATED ALWAYS."
    },
    {
      "kind": "primary",
      "literal": "Column \" \" is an identity column defined as GENERATED ALWAYS.",
      "position": 2,
      "role": "errdetail",
      "template": "Column \"%s\" is an identity column defined as GENERATED ALWAYS."
    }
  ]
}
messages · message.variant-4
{
  "limits": "Placeholders remain dynamic source fields; no concrete runtime value is claimed.",
  "message_id": "message.variant-4",
  "path": "",
  "position": 3,
  "raw": {
    "detail_templates": [
      "Column \"%s\" is a generated column."
    ],
    "hint_templates": [],
    "id": "message.variant-4",
    "limits": "Placeholders remain dynamic source fields; no concrete runtime value is claimed.",
    "primary_template": "column \"%s\" can only be updated to DEFAULT",
    "roles": [
      {
        "role": "errmsg",
        "template": "column \"%s\" can only be updated to DEFAULT"
      },
      {
        "role": "errdetail",
        "template": "Column \"%s\" is a generated column."
      }
    ],
    "severity_source": "explicit ERROR",
    "sources": [
      "src.call.428C9.generated-update"
    ],
    "sqlstate": "428C9"
  },
  "severity": "explicit ERROR",
  "sources": [
    "src.call.428C9.generated-update"
  ],
  "templates": [
    {
      "kind": "primary",
      "literal": "column \" \" can only be updated to DEFAULT",
      "position": 0,
      "role": "",
      "template": "column \"%s\" can only be updated to DEFAULT"
    },
    {
      "kind": "detail",
      "literal": "Column \" \" is a generated column.",
      "position": 1,
      "role": "",
      "template": "Column \"%s\" is a generated column."
    },
    {
      "kind": "primary",
      "literal": "Column \" \" is a generated column.",
      "position": 2,
      "role": "errdetail",
      "template": "Column \"%s\" is a generated column."
    }
  ]
}
sources · src.errcodes.428C9.18.6
{
  "commit": "724edf9bde9d356724ad384a2e196edc3c9f80f7",
  "docs_url": "",
  "kind": "upstream_source",
  "location": "line 355",
  "path": "src/backend/utils/errcodes.txt",
  "position": 0,
  "sha256": "6e8de346643ba84aa3c9c6a73360acfc7b2dfb89162c06c08ce9bf5bcd5bbcba",
  "source_id": "src.errcodes.428C9.18.6",
  "tag": "REL_18_6",
  "url": "https://github.com/postgres/postgres/blob/724edf9bde9d356724ad384a2e196edc3c9f80f7/src/backend/utils/errcodes.txt#L355"
}
sources · src.call.428C9.353c4cc772b5ae9adad09c67
{
  "commit": "724edf9bde9d356724ad384a2e196edc3c9f80f7",
  "docs_url": "",
  "kind": "upstream_source",
  "location": "lines 942-948",
  "path": "src/backend/rewrite/rewriteHandler.c",
  "position": 1,
  "sha256": "5a34bf31f424abbf46cd926da1fefcd9360c4d527a87d8a0ac9f653d67c2206a",
  "source_id": "src.call.428C9.353c4cc772b5ae9adad09c67",
  "tag": "REL_18_6",
  "url": "https://github.com/postgres/postgres/blob/724edf9bde9d356724ad384a2e196edc3c9f80f7/src/backend/rewrite/rewriteHandler.c#L942-L948"
}
sources · src.call.428C9.47aaa60ecb1566d462ffaa86
{
  "commit": "724edf9bde9d356724ad384a2e196edc3c9f80f7",
  "docs_url": "",
  "kind": "upstream_source",
  "location": "lines 981-986",
  "path": "src/backend/rewrite/rewriteHandler.c",
  "position": 2,
  "sha256": "5a34bf31f424abbf46cd926da1fefcd9360c4d527a87d8a0ac9f653d67c2206a",
  "source_id": "src.call.428C9.47aaa60ecb1566d462ffaa86",
  "tag": "REL_18_6",
  "url": "https://github.com/postgres/postgres/blob/724edf9bde9d356724ad384a2e196edc3c9f80f7/src/backend/rewrite/rewriteHandler.c#L981-L986"
}
sources · src.call.428C9.8585d423db2446a6222cbf57
{
  "commit": "724edf9bde9d356724ad384a2e196edc3c9f80f7",
  "docs_url": "",
  "kind": "upstream_source",
  "location": "lines 1008-1013",
  "path": "src/backend/rewrite/rewriteHandler.c",
  "position": 3,
  "sha256": "5a34bf31f424abbf46cd926da1fefcd9360c4d527a87d8a0ac9f653d67c2206a",
  "source_id": "src.call.428C9.8585d423db2446a6222cbf57",
  "tag": "REL_18_6",
  "url": "https://github.com/postgres/postgres/blob/724edf9bde9d356724ad384a2e196edc3c9f80f7/src/backend/rewrite/rewriteHandler.c#L1008-L1013"
}
sources · src.call.428C9.generated-update
{
  "commit": "724edf9bde9d356724ad384a2e196edc3c9f80f7",
  "docs_url": "",
  "kind": "upstream_source",
  "location": "lines 1015-1030",
  "path": "src/backend/rewrite/rewriteHandler.c",
  "position": 4,
  "sha256": "5a34bf31f424abbf46cd926da1fefcd9360c4d527a87d8a0ac9f653d67c2206a",
  "source_id": "src.call.428C9.generated-update",
  "tag": "REL_18_6",
  "url": "https://github.com/postgres/postgres/blob/724edf9bde9d356724ad384a2e196edc3c9f80f7/src/backend/rewrite/rewriteHandler.c#L1015-L1030"
}
sources · src.calls.REL_18_6.428C9
{
  "commit": "",
  "docs_url": "",
  "kind": "local_artifact",
  "location": "",
  "path": "raw/calls/REL_18_6.jsonl",
  "position": 5,
  "sha256": "9ee8a0e81d8f0825c5c1ae45583439859a26e602bdd4ce2f2a62aa278867ccbf",
  "source_id": "src.calls.REL_18_6.428C9",
  "tag": "",
  "url": ""
}

Source references

Full definition and evidence JSON

Definition sources

center · PostgreSQL 18 · english-manuals:22585bb6ff2709be54a90631e37a8ab999ad6ae228e1398619eda4ec68aae592

Text language: en · english-manuals:22585bb6ff2709be54a90631e37a8ab999ad6ae228e1398619eda4ec68aae592