# 42804 — Datatype mismatch > PostgreSQL SQLSTATE 42804: Datatype mismatch (datatype_mismatch), source-backed diagnosis and recovery guidance. --- # 42804 — Datatype mismatch ## At a glance {#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. | Field | Value | | --- | --- | | SQLSTATE | `42804` | | Condition | `datatype_mismatch` | | Status | `active` | | Known present by | `7.4` | | Locked snapshots | `9.0.23, 9.1.24, 9.2.24, 9.3.25, 9.4.26, 9.5.25, 9.6.24, 10.23, 11.22, 12.22, 13.23, 14.24, 15.19, 16.15, 17.11, 18.6, 19beta3` | | Macros | `ERRCODE_DATATYPE_MISMATCH` | | Aliases | `—` | ## Meaning {#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 {#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 {#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 {#messages} 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 {#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. ```sql 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`](../data/cases/42804.json) and [`authored evidence`](../data/evidence/42804.json); private manifest and registry hashes are recorded there. ## Versions {#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 {#related} - [`22P02`](../22p02/) - [`42846`](../42846/) - [`42803`](../42803/) ## Sources {#sources} - `src.errcodes.REL_18_6` — fixed definition at `724edf9bde9d356724ad384a2e196edc3c9f80f7`; SHA-256 `6e8de346643ba84aa3c9c6a73360acfc7b2dfb89162c06c08ce9bf5bcd5bbcba` ([source](https://github.com/postgres/postgres/blob/724edf9bde9d356724ad384a2e196edc3c9f80f7/src/backend/utils/errcodes.txt#L1-L1)). - `src.datatype-default.18.6` — `src/backend/catalog/heap.c` lines 3413–3420 at `724edf9bde9d356724ad384a2e196edc3c9f80f7`; blob SHA-256 `e720ee58279590793edd0985b3c910970ef56e7361b8ef92e245370587e02a0e` ([source](https://github.com/postgres/postgres/blob/724edf9bde9d356724ad384a2e196edc3c9f80f7/src/backend/catalog/heap.c#L3413-L3420)). - `src.datatype-default.10.23` — `src/backend/catalog/heap.c` lines 2679–2686 at `02991e79f8f58bc208f05dcc8af0c62dbe0a6ea4`; blob SHA-256 `285e1e2f8024acb58497735bcbd8e605e7cfdd224e442becdee6a5b0e7ac7cf6` ([source](https://github.com/postgres/postgres/blob/02991e79f8f58bc208f05dcc8af0c62dbe0a6ea4/src/backend/catalog/heap.c#L2679-L2686)). - `src.assignment-target.18.6` — `src/backend/parser/parse_target.c` lines 575–596 at `724edf9bde9d356724ad384a2e196edc3c9f80f7`; blob SHA-256 `4f0cda351acc3e2854bf55514e69cf8b7de6ad82e1535045e4523581ca0d943c` ([source](https://github.com/postgres/postgres/blob/724edf9bde9d356724ad384a2e196edc3c9f80f7/src/backend/parser/parse_target.c#L575-L596)). - `src.assignment-target.10.23` — `src/backend/parser/parse_target.c` lines 570–596 at `02991e79f8f58bc208f05dcc8af0c62dbe0a6ea4`; blob SHA-256 `808bfadd7717d711bc71eb85b7ecd343c742ccc8f6f3087164ed20da098b6d7d` ([source](https://github.com/postgres/postgres/blob/02991e79f8f58bc208f05dcc8af0c62dbe0a6ea4/src/backend/parser/parse_target.c#L570-L596)). - `src.common-type.18.6` — `src/backend/parser/parse_coerce.c` lines 1327–1425 at `724edf9bde9d356724ad384a2e196edc3c9f80f7`; blob SHA-256 `6ac3ddab06edff28e9a694c64fb7f559315cf488bfa4392bc14c24e559f6bd2e` ([source](https://github.com/postgres/postgres/blob/724edf9bde9d356724ad384a2e196edc3c9f80f7/src/backend/parser/parse_coerce.c#L1327-L1425)). - `src.common-type.10.23` — `src/backend/parser/parse_coerce.c` lines 1217–1311 at `02991e79f8f58bc208f05dcc8af0c62dbe0a6ea4`; blob SHA-256 `ed778c872fe4f2b8742828795566c3eaa24e7af3ce747fbf844c374d673593d3` ([source](https://github.com/postgres/postgres/blob/02991e79f8f58bc208f05dcc8af0c62dbe0a6ea4/src/backend/parser/parse_coerce.c#L1217-L1311)). - `src.common-coerce.18.6` — `src/backend/parser/parse_coerce.c` lines 1564–1593 at `724edf9bde9d356724ad384a2e196edc3c9f80f7`; blob SHA-256 `6ac3ddab06edff28e9a694c64fb7f559315cf488bfa4392bc14c24e559f6bd2e` ([source](https://github.com/postgres/postgres/blob/724edf9bde9d356724ad384a2e196edc3c9f80f7/src/backend/parser/parse_coerce.c#L1564-L1593)). - `src.common-coerce.10.23` — `src/backend/parser/parse_coerce.c` lines 1349–1378 at `02991e79f8f58bc208f05dcc8af0c62dbe0a6ea4`; blob SHA-256 `ed778c872fe4f2b8742828795566c3eaa24e7af3ce747fbf844c374d673593d3` ([source](https://github.com/postgres/postgres/blob/02991e79f8f58bc208f05dcc8af0c62dbe0a6ea4/src/backend/parser/parse_coerce.c#L1349-L1378)). - `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.