42804

At a glance

42804 is datatype_mismatch: an expression or definition has a type different from its target context. The selected path rejects a text default for an integer column.

Meaning

The cookDefault path reports column "%s" is of type %s but default expression is of type %s, with a rewrite/cast hint. The same SQLSTATE also covers assignment coercion for INSERT/UPDATE targets (column "%s" is of type %s but expression is of type %s) and common-type selection in CASE/UNION (%s types %s and %s cannot be matched). This differs from 22P02 (the chosen type's input parser rejects a value) and 42846 (a selected common target has no coercion path). Inspect pg_attribute.atttypid, pg_type, pg_get_expr, and the actual expression context; the failed autocommit CREATE leaves IDLE.

Diagnosis

Identify the target column or result type, then inspect pg_attribute.atttypid, pg_type, and pg_get_expr for stored defaults or generated expressions. For INSERT/UPDATE, compare the assigned expression's type with the target column. For CASE/UNION, inspect every arm or input that participates in common-type selection. Read the expanded source and target types and the cursor position from the diagnostic before choosing a cast.

Response

Make the expression produce the target type or add a deliberate valid cast. For an assignment, fix the producer or cast it to the intended column type; for CASE/UNION, make the branches share an intended common type. Do not repair a 22P02 input failure by changing text and call that a type match, and do not treat a missing coercion path as 42804 when the source branch is 42846. The case uses DEFAULT 1, inserts DEFAULT VALUES, and reads back 1. In an explicit transaction, a failed definition or assignment leaves INERROR; roll back or use a suitable savepoint before retrying. The selected autocommit failure is the path that returns to IDLE.

Observed diagnostics

The fixed groups are explicit ERROR variants. The selected heap.c primary is column "%s" is of type %s but default expression is of type %s, with hint You will need to rewrite or cast the expression. The assignment path uses column "%s" is of type %s but expression is of type %s with the same hint. The common-type path uses %s types %s and %s cannot be matched for CASE/UNION-style contexts. A failed value input is 22P02; a later inability to coerce an expression to the selected common type is 42846.

Representative case

The registry first defines an integer column with an explicitly text default, then creates the matching definition, inserts a default row, and verifies it.

CREATE TABLE syntax_schema.mismatch_table (value integer DEFAULT 'x'::text);
CREATE TABLE syntax_schema.mismatch_table (value integer DEFAULT 1);
INSERT INTO syntax_schema.mismatch_table DEFAULT VALUES;
SELECT value FROM syntax_schema.mismatch_table;

The selected 18.6 and 10.21 runs passed SQLSTATE, severity, state/recovery, repair, cleanup, and isolated-target stop assertions. See case JSON and authored evidence; private manifest and registry hashes are recorded there.

Versions

The locked catalogue contains this condition from the 7.4 presence bound through the listed snapshots. The selected natural case passed on PostgreSQL 18.6 and 10.21; that bounded result does not infer every intermediate release or every source branch.

Related

• 22P02
• 42846
• 42803

Sources

• src.errcodes.REL_18_6 — fixed definition at 724edf9bde9d356724ad384a2e196edc3c9f80f7; SHA-256 6e8de346643ba84aa3c9c6a73360acfc7b2dfb89162c06c08ce9bf5bcd5bbcba (source).
• src.datatype-default.18.6 — src/backend/catalog/heap.c lines 3413–3420 at 724edf9bde9d356724ad384a2e196edc3c9f80f7; blob SHA-256 e720ee58279590793edd0985b3c910970ef56e7361b8ef92e245370587e02a0e (source).
• src.datatype-default.10.23 — src/backend/catalog/heap.c lines 2679–2686 at 02991e79f8f58bc208f05dcc8af0c62dbe0a6ea4; blob SHA-256 285e1e2f8024acb58497735bcbd8e605e7cfdd224e442becdee6a5b0e7ac7cf6 (source).
• src.assignment-target.18.6 — src/backend/parser/parse_target.c lines 575–596 at 724edf9bde9d356724ad384a2e196edc3c9f80f7; blob SHA-256 4f0cda351acc3e2854bf55514e69cf8b7de6ad82e1535045e4523581ca0d943c (source).
• src.assignment-target.10.23 — src/backend/parser/parse_target.c lines 570–596 at 02991e79f8f58bc208f05dcc8af0c62dbe0a6ea4; blob SHA-256 808bfadd7717d711bc71eb85b7ecd343c742ccc8f6f3087164ed20da098b6d7d (source).
• src.common-type.18.6 — src/backend/parser/parse_coerce.c lines 1327–1425 at 724edf9bde9d356724ad384a2e196edc3c9f80f7; blob SHA-256 6ac3ddab06edff28e9a694c64fb7f559315cf488bfa4392bc14c24e559f6bd2e (source).
• src.common-type.10.23 — src/backend/parser/parse_coerce.c lines 1217–1311 at 02991e79f8f58bc208f05dcc8af0c62dbe0a6ea4; blob SHA-256 ed778c872fe4f2b8742828795566c3eaa24e7af3ce747fbf844c374d673593d3 (source).
• src.common-coerce.18.6 — src/backend/parser/parse_coerce.c lines 1564–1593 at 724edf9bde9d356724ad384a2e196edc3c9f80f7; blob SHA-256 6ac3ddab06edff28e9a694c64fb7f559315cf488bfa4392bc14c24e559f6bd2e (source).
• src.common-coerce.10.23 — src/backend/parser/parse_coerce.c lines 1349–1378 at 02991e79f8f58bc208f05dcc8af0c62dbe0a6ea4; blob SHA-256 ed778c872fe4f2b8742828795566c3eaa24e7af3ce747fbf844c374d673593d3 (source).
• src.calls.REL_18_6 / src.calls.REL_10_23 — fixed call scans, SHA-256 9ee8a0e81d8f0825c5c1ae45583439859a26e602bdd4ce2f2a62aa278867ccbf / 00d16d3eb01b71ccf1b245c8f3102f9d0ec9f36fb02777b8dd1b99fcb263040c.
• manifest.42804 / snippet-registry.42804 — hashes are recorded in evidence/42804.json and each runtime record.

Source evidence

Evidence belongs to the frozen source and runtime versions listed here. It is not a runtime verification of the selected manual version.

• src/backend/utils/errcodes.txt · REL_18_6 ·
  724edf9bde9d356724ad384a2e196edc3c9f80f7
• raw/calls/REL_18_6.jsonl · ·
• raw/calls/REL_10_23.jsonl · ·
• src/backend/catalog/heap.c · REL_18_6 ·
  724edf9bde9d356724ad384a2e196edc3c9f80f7
• src/backend/catalog/heap.c · REL_10_23 ·
  02991e79f8f58bc208f05dcc8af0c62dbe0a6ea4
• src/backend/parser/parse_target.c · REL_18_6 ·
  724edf9bde9d356724ad384a2e196edc3c9f80f7
• src/backend/parser/parse_target.c · REL_10_23 ·
  02991e79f8f58bc208f05dcc8af0c62dbe0a6ea4
• src/backend/parser/parse_coerce.c · REL_18_6 ·
  724edf9bde9d356724ad384a2e196edc3c9f80f7
• src/backend/parser/parse_coerce.c · REL_10_23 ·
  02991e79f8f58bc208f05dcc8af0c62dbe0a6ea4
• src/backend/parser/parse_coerce.c · REL_18_6 ·
  724edf9bde9d356724ad384a2e196edc3c9f80f7
• src/backend/parser/parse_coerce.c · REL_10_23 ·
  02991e79f8f58bc208f05dcc8af0c62dbe0a6ea4
• verify/cases/42804/cases.json · ·
• verify/cases/42804/snippets.json · ·

Message templates

column "%s" is of type %s but default expression is of type %s

You will need to rewrite or cast the expression.

column "%s" is of type %s but expression is of type %s

You will need to rewrite or cast the expression.

%s types %s and %s cannot be matched

Reproduction & repair cases

Recorded runtime evidence

{
  "diagnostic": {
    "text": "column \"value\" is of type integer but default expression is of type text\nHINT:  You will need to rewrite or cast the expression.",
    "context": null,
    "severity": "ERROR",
    "sqlstate": "42804",
    "table_name": null,
    "column_name": null,
    "schema_name": null,
    "source_file": "heap.c",
    "source_line": "3420",
    "message_hint": "You will need to rewrite or cast the expression.",
    "datatype_name": null,
    "exception_type": "DatatypeMismatch",
    "internal_query": null,
    "message_detail": null,
    "constraint_name": null,
    "message_primary": "column \"value\" is of type integer but default expression is of type text",
    "source_function": "cookDefault",
    "internal_position": null,
    "statement_position": null,
    "severity_nonlocalized": "ERROR"
  },
  "repair_value": 1,
  "failed_status": "IDLE",
  "repair_status": "IDLE",
  "connection_autocommit": true
}

{
  "diagnostic": {
    "text": "column \"value\" is of type integer but default expression is of type text\nHINT:  You will need to rewrite or cast the expression.",
    "context": null,
    "severity": "ERROR",
    "sqlstate": "42804",
    "table_name": null,
    "column_name": null,
    "schema_name": null,
    "source_file": "heap.c",
    "source_line": "2686",
    "message_hint": "You will need to rewrite or cast the expression.",
    "datatype_name": null,
    "exception_type": "DatatypeMismatch",
    "internal_query": null,
    "message_detail": null,
    "constraint_name": null,
    "message_primary": "column \"value\" is of type integer but default expression is of type text",
    "source_function": "cookDefault",
    "internal_position": null,
    "statement_position": null,
    "severity_nonlocalized": "ERROR"
  },
  "repair_value": 1,
  "failed_status": "IDLE",
  "repair_status": "IDLE",
  "connection_autocommit": true
}