{"record":{"id":"1f1782912358e122","repo":"pathwaycom/pathway","slug":"primary-key-column-pkey-name-r-is-declared-nulla","errorCode":null,"errorMessage":"primary_key column {pkey.name!r} is declared nullable ({pkey._column.dtype}); primary-key columns must be non-nullable in snapshot mode.","messagePattern":"primary_key column (.+?) is declared nullable \\((.+?)\\); primary-key columns must be non-nullable in snapshot mode\\.","errorType":"validation","errorClass":"ValueError","httpStatus":null,"severity":"error","filePath":"python/pathway/io/duckdb/__init__.py","lineNumber":349,"sourceCode":"        for pkey in primary_key:\n            if pkey.name in names_seen and pkey.name not in duplicates:\n                duplicates.append(pkey.name)\n            names_seen.add(pkey.name)\n        if duplicates:\n            raise ValueError(\n                f\"primary_key contains duplicate column(s) {sorted(duplicates)}. \"\n                \"Each column may appear at most once.\"\n            )\n        key_field_names = []\n        for pkey in primary_key:\n            # Raises ValueError when `pkey` belongs to a different table or does\n            # not name a column of `table`, so users get a clear message at\n            # write() time instead of an opaque runtime error.\n            get_column_index(table, pkey)\n            # A nullable primary key makes DELETE ... WHERE pk = NULL never match\n            # on retractions, so the destination would keep stale rows.\n            if isinstance(pkey._column.dtype, dt.Optional):\n                raise ValueError(\n                    f\"primary_key column {pkey.name!r} is declared nullable \"\n                    f\"({pkey._column.dtype}); primary-key columns must be \"\n                    \"non-nullable in snapshot mode.\"\n                )\n            # DuckDB cannot build a PRIMARY KEY / index on a list, array, tuple or\n            # JSON column, so such a column can never serve as a snapshot primary\n            # key — the CREATE TABLE (or the upsert's ON CONFLICT) would fail with\n            # an opaque \"Invalid type for index key\" error mid-run.\n            if isinstance(pkey._column.dtype, (dt.List, dt.Array, dt.Tuple)) or (\n                pkey._column.dtype == dt.JSON\n            ):\n                raise ValueError(\n                    f\"primary_key column {pkey.name!r} has non-scalar type \"\n                    f\"({pkey._column.dtype}); DuckDB cannot index list, array, \"\n                    \"tuple or JSON columns, so they cannot be used as a snapshot \"\n                    \"primary key.\"\n                )\n            key_field_names.append(pkey.name)","sourceCodeStart":331,"sourceCodeEnd":367,"githubUrl":"https://github.com/pathwaycom/pathway/blob/fa2f74a4649b7c5908690cf60137263d8d80de5f/python/pathway/io/duckdb/__init__.py#L331-L367","documentation":"Raised by pw.io.duckdb.write in snapshot mode when a column used in primary_key is declared Optional (nullable) in the Pathway schema. A nullable key makes DELETE ... WHERE pk = NULL never match on retractions, so the destination table would keep stale rows forever; the connector rejects it at write() time.","triggerScenarios":"pw.io.duckdb.write(t, table_name=\"t\", output_table_type=\"snapshot\", primary_key=t.user_id) where the schema declares user_id: Optional[int] or column_definition(secondary_key=True).","commonSituations":"Schemas generated from JSON/CSV sources where every column is optional by default; using a column that is genuinely sometimes missing as the key.","solutions":["Make the key column required in the schema: class Input(pw.Schema, id: int) instead of id: Optional[int].","Pick a different, non-nullable column as the primary key.","If the value can be missing, fill it before writing (e.g. .fillna()) and declare the schema column non-optional."],"exampleFix":"# before\nclass Input(pw.Schema):\n    user_id: Optional[int]\npw.io.duckdb.write(t, table_name=\"t\", output_table_type=\"snapshot\", primary_key=t.user_id)\n\n# after\nclass Input(pw.Schema):\n    user_id: int\npw.io.duckdb.write(t, table_name=\"t\", output_table_type=\"snapshot\", primary_key=t.user_id)","handlingStrategy":"validation","validationCode":"from pathway import dt\nfor pk in primary_key or []:\n    assert not isinstance(pk._column.dtype, dt.Optional), f\"nullable key column: {pk.name}\"","typeGuard":"def is_non_nullable_key(column) -> bool:\n    return not isinstance(column._column.dtype, __import__('pathway').dt.Optional)","tryCatchPattern":null,"preventionTips":["Declare required schema fields as non-Optional; pathway's JSON/CSV readers make columns Optional only if you ask.","Pick identity columns that are guaranteed present in every row."],"tags":["duckdb","primary-key","schema","nullable"],"backgroundTag":null,"analyzedSha":"fa2f74a4649b7c5908690cf60137263d8d80de5f","analyzedAt":"2026-08-15T01:48:17.006Z","schemaVersion":2},"datasetVersion":"2026-08-15T22:17:37.221Z"}