{"record":{"id":"1087a5a7bfc1e6cf","repo":"pathwaycom/pathway","slug":"primary-key-column-pkey-field-name-is-declared","errorCode":null,"errorMessage":"primary_key column '{pkey_field.name}' is declared nullable; primary_key columns must be non-nullable in snapshot mode. Either remove the Optional wrapper in the schema or filter out nulls upstream via .filter(t.pkey.is_not_none()).","messagePattern":"primary_key column '(.+?)' is declared nullable; primary_key columns must be non-nullable in snapshot mode\\. Either remove the Optional wrapper in the schema or filter out nulls upstream via \\.filter\\(t\\.pkey\\.is_not_none\\(\\)\\)\\.","errorType":"exception","errorClass":"ValueError","httpStatus":null,"severity":"error","filePath":"python/pathway/io/postgres/__init__.py","lineNumber":944,"sourceCode":"        # ``primary_key_fields`` index-swap used on DELETE. Reject it\n        # here with a clear message instead.\n        duplicates = sorted(\n            {name for name in key_field_names if key_field_names.count(name) > 1}\n        )\n        if duplicates:\n            raise ValueError(f\"primary_key contains duplicate column(s) {duplicates}\")\n        # A nullable primary-key column in snapshot mode is silently\n        # broken: either the `NOT NULL` PRIMARY KEY we emit on\n        # create_if_not_exists / replace rejects the NULL row at\n        # insert time, or (against a pre-existing table that allows\n        # NULL in the PK) the retraction ``DELETE ... WHERE pkey=$1``\n        # never matches anything because SQL ``= NULL`` is always\n        # false. Both are data-loss footguns, so we refuse the setup\n        # here with an actionable message.\n        if is_snapshot_mode:\n            for pkey_field in primary_key:\n                if isinstance(pkey_field._column.dtype, dtype.Optional):\n                    raise ValueError(\n                        f\"primary_key column '{pkey_field.name}' is \"\n                        \"declared nullable; primary_key columns must be \"\n                        \"non-nullable in snapshot mode. Either remove \"\n                        \"the Optional wrapper in the schema or filter \"\n                        \"out nulls upstream via \"\n                        \".filter(t.pkey.is_not_none()).\"\n                    )\n    data_format = api.DataFormat(\n        format_type=\"identity\",\n        key_field_names=key_field_names,\n        value_fields=_format_output_value_fields(table),\n        table_name=table_name,\n        external_diff_column_index=external_diff_column_index,\n    )\n\n    datasink_type = \"snapshot\" if is_snapshot_mode else \"sink\"\n    table.to(\n        datasink.GenericDataSink(","sourceCodeStart":926,"sourceCodeEnd":962,"githubUrl":"https://github.com/pathwaycom/pathway/blob/fa2f74a4649b7c5908690cf60137263d8d80de5f/python/pathway/io/postgres/__init__.py#L926-L962","documentation":"Raised by pw.io.postgres.write in snapshot mode (output_table_type='snapshot' or the write_snapshot path) when a primary_key column is typed Optional. Snapshot mode writes PRIMARY KEY ... NOT NULL on create_if_not_exists/replace (so NULL rows fail at INSERT), and against a pre-existing table with a nullable PK the DELETE ... WHERE pkey=$1 retraction never matches because SQL = NULL is always false. Both outcomes silently lose data, so Pathway refuses the configuration up front.","triggerScenarios":"pw.io.postgres.write(t, conn, 't', primary_key=[t.id], output_table_type='snapshot') where the schema declares id: Optional[int] (or any Optional type), or pw.io.postgres.write_snapshot with a nullable key column.","commonSituations":"Schemas generated from JSON/CSV inputs where every column defaults to Optional; ingesting raw data whose key column can be None; migrating an existing pipeline to snapshot mode without tightening the schema.","solutions":["Remove the Optional wrapper on the key column in the schema class (id: int instead of id: Optional[int]).","If nulls genuinely occur, filter them out upstream before writing: t = t.filter(t.id.is_not_none()), which also narrows the type to non-optional.","If the data cannot be trusted, cast with .unwrap() or apply a default so the column is non-nullable by the time it reaches write()."],"exampleFix":"# before\nclass InputSchema(pw.Schema):\n    id: Optional[int]\n    value: str\n\npw.io.postgres.write(t, conn, \"t\", primary_key=[t.id], output_table_type=\"snapshot\")\n\n# after\nclass InputSchema(pw.Schema):\n    id: int\n    value: str\n\npw.io.postgres.write(t, conn, \"t\", primary_key=[t.id], output_table_type=\"snapshot\")","handlingStrategy":"validation","validationCode":"key_col = \"id\"\nassert not t.schema.columns()[key_col].dtype.is_optional, \\\n    f\"primary key column {key_col!r} must not be Optional\"\npw.io.postgres.write(t, conn, \"t\", primary_key=[t.id], output_table_type=\"snapshot\")","typeGuard":"def key_is_not_optional(t: \"pw.Table\", col: str) -> bool:\n    return not t.schema.columns()[col].dtype.is_optional","tryCatchPattern":null,"preventionTips":["Keep primary-key schema fields non-Optional from the start; only value columns may be Optional.","Filter nulls early: t = t.filter(t.id.is_not_none()) — it fixes the type and cleans the data.","Treat Optional keys as a schema smell in code review."],"tags":["pathway","postgres","primary-key","nullable","snapshot"],"backgroundTag":null,"analyzedSha":"fa2f74a4649b7c5908690cf60137263d8d80de5f","analyzedAt":"2026-08-15T01:48:17.006Z","schemaVersion":2},"datasetVersion":"2026-08-15T22:17:37.221Z"}