↑↓ 选择↵ 打开⌫ 切换范围完整搜索

PG.CENTER 连接 PostgreSQL 文档、百科与生态知识。由 Pigsty 维护。

Wiki / SQLSTATE

428C9 — GENERATED ALWAYS 列赋值

向身份列或生成列赋值，这类列只接受 DEFAULT。

英文手册覆盖始于 PostgreSQL 8.1. 来源历史与运行验证各自保留独立版本边界。

当前阅读 PG 18·选择有来源记录的版本

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

版本定义 PG 18

428C9 — GENERATED ALWAYS 列赋值

速览 {#at-a-glance}

428C9（generated_always）是重写阶段的 ERROR：INSERT 或 UPDATE 向必须由服务器生成或只接受 DEFAULT 的列提供了值。身份列和生成列的赋值规则不同，必须结合操作类型和列元数据判断。

含义 {#meaning}

重写器会把 INSERT 中省略的目标列或显式 DEFAULT 标为 apply_default。GENERATED ALWAYS 身份列收到非默认值时，除非使用 OVERRIDING SYSTEM VALUE，否则报错；OVERRIDING USER VALUE 会强制使用默认生成。BY DEFAULT 身份列通常允许显式值，但 OVERRIDING USER VALUE 仍会强制生成。生成列无论是否有 OVERRIDING 都只接受 DEFAULT，INSERT 的 DETAIL 会指出列名，身份列分支还带 HINT Use OVERRIDING SYSTEM VALUE to override.

UPDATE 的检查覆盖 GENERATED ALWAYS 身份列和所有生成列，只要目标项是非默认值就报错；UPDATE 没有 OVERRIDING 例外。检查之后，虚拟生成列在重写目标中放入 NULL，存储生成列由执行器填充。这是 18.6 固定源码的存储路径；目录中“10.0 已知存在”只是 SQLSTATE 定义下界，不能据此声称 10.0 已具备所有生成列特性。

诊断 {#diagnosis}

检查 pg_attribute.attidentity（a 表示 ALWAYS、d 表示 BY DEFAULT）和 attgenerated（s 表示存储、v 表示虚拟），确认是 INSERT 还是 UPDATE，以及目标项是省略、DEFAULT 还是实际值。VALUES 来源还要确认对应行项是否确实为 DEFAULT；重写器有单独的全默认判断。身份列与生成列的 INSERT 可能共用主消息，需同时读取 DETAIL/HINT。

处理 {#response}

GENERATED ALWAYS 身份列省略赋值、使用 DEFAULT，或仅在业务明确拥有身份值时使用 OVERRIDING SYSTEM VALUE。BY DEFAULT 身份列可按业务保留显式值；需要服务器生成时用 OVERRIDING USER VALUE。生成列移除赋值，不要用类型转换绕过检查。若 ERROR 发生在显式事务中，应回滚到合适保存点或回滚事务后再发修正语句；本页没有运行时恢复观察。

消息变体 {#messages}

选定的 18.6 分支均为 ERROR：

| 操作 | 主消息 | DETAIL | HINT | | --- | --- | --- | --- | | 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. | | 生成列 INSERT | cannot insert a non-DEFAULT value into column "%s" | Column "%s" is a generated column. | 无 | | GENERATED ALWAYS 身份列 UPDATE | column "%s" can only be updated to DEFAULT | Column "%s" is an identity column defined as GENERATED ALWAYS. | 无 | | 生成列 UPDATE | column "%s" can only be updated to DEFAULT | Column "%s" is a generated column. | 无 |

固定源码还区分虚拟生成列的 NULL 目标项与存储生成列的执行器填充。每个 %s 都是动态字段，本页不声称具体占位符值或运行时结果。

版本 {#versions}

目录记录该条件自 10.0 起存在，并锁定到 18.6、19beta3；这是定义下界，不是身份列、存储生成列和虚拟生成列共同的功能引入版本。本页机制依据 18.6 固定源码，未运行数据库。

相关 {#related}

来源 {#sources}

  • src.errcodes.428C9.18.6 — src/backend/utils/errcodes.txt line 355，固定于 724edf9bde9d356724ad384a2e196edc3c9f80f7；SHA-256 6e8de346643ba84aa3c9c6a73360acfc7b2dfb89162c06c08ce9bf5bcd5bbcba（源码）。

  • src.call.428C9.353c4cc772b5ae9adad09c67 — src/backend/rewrite/rewriteHandler.c lines 942-948，固定于 724edf9bde9d356724ad384a2e196edc3c9f80f7；SHA-256 5a34bf31f424abbf46cd926da1fefcd9360c4d527a87d8a0ac9f653d67c2206a（源码）。

  • src.call.428C9.47aaa60ecb1566d462ffaa86 — src/backend/rewrite/rewriteHandler.c lines 981-986，固定于 724edf9bde9d356724ad384a2e196edc3c9f80f7；SHA-256 5a34bf31f424abbf46cd926da1fefcd9360c4d527a87d8a0ac9f653d67c2206a（源码）。

  • src.call.428C9.8585d423db2446a6222cbf57 — src/backend/rewrite/rewriteHandler.c lines 1008-1013，固定于 724edf9bde9d356724ad384a2e196edc3c9f80f7；SHA-256 5a34bf31f424abbf46cd926da1fefcd9360c4d527a87d8a0ac9f653d67c2206a（源码）。

  • src.call.428C9.generated-update — src/backend/rewrite/rewriteHandler.c lines 1015-1030，固定于 724edf9bde9d356724ad384a2e196edc3c9f80f7；SHA-256 5a34bf31f424abbf46cd926da1fefcd9360c4d527a87d8a0ac9f653d67c2206a（源码）。

  • src.calls.REL_18_6.428C9 — 固定本地核心调用扫描；SHA-256 9ee8a0e81d8f0825c5c1ae45583439859a26e602bdd4ce2f2a62aa278867ccbf。

  • 作者证据 — 记录源码断言、消息角色和运行边界。

比较版本

SQLSTATE 证据与已记录的运行版本

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": ""
}

来源引用

完整定义与证据 JSON

定义来源

center · PostgreSQL 18 · english-manuals:22585bb6ff2709be54a90631e37a8ab999ad6ae228e1398619eda4ec68aae592

正文语言: zh-Hans · english-manuals:22585bb6ff2709be54a90631e37a8ab999ad6ae228e1398619eda4ec68aae592