{"record":{"id":"9fbf79596d8dc0de","repo":"pathwaycom/pathway","slug":"primary-key-column-pkey-name-r-has-non-scalar-ty","errorCode":null,"errorMessage":"primary_key column {pkey.name!r} has non-scalar type ({pkey._column.dtype}); DuckDB cannot index list, array, tuple or JSON columns, so they cannot be used as a snapshot primary key.","messagePattern":"primary_key column (.+?) has non-scalar type \\((.+?)\\); DuckDB cannot index list, array, tuple or JSON columns, so they cannot be used as a snapshot primary key\\.","errorType":"validation","errorClass":"ValueError","httpStatus":null,"severity":"error","filePath":"python/pathway/io/duckdb/__init__.py","lineNumber":361,"sourceCode":"            # 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)\n\n    data_storage = api.DataStorage(\n        storage_type=\"duckdb\",\n        path=database_str,\n        table_name=table_name,\n        table_writer_init_mode=init_mode_from_str(init_mode),\n        max_batch_size=max_batch_size,\n        snapshot_maintenance_on_output=is_snapshot_mode,\n        detach_between_batches=detach_between_batches,\n    )\n    data_format = api.DataFormat(\n        format_type=\"identity\",","sourceCodeStart":343,"sourceCodeEnd":379,"githubUrl":"https://github.com/pathwaycom/pathway/blob/fa2f74a4649b7c5908690cf60137263d8d80de5f/python/pathway/io/duckdb/__init__.py#L343-L379","documentation":"Raised by pw.io.duckdb.write when a primary_key column has a non-scalar type (list, array, tuple, or JSON). DuckDB cannot build a PRIMARY KEY or index on those types, so CREATE TABLE or the upsert's ON CONFLICT would fail mid-run with an opaque 'Invalid type for index key' error; the connector rejects it up front.","triggerScenarios":"pw.io.duckdb.write(t, table_name=\"t\", output_table_type=\"snapshot\", primary_key=t.payload) where payload is dt.List(...), dt.Array, a tuple/JSON column, or a string column typed as dt.JSON.","commonSituations":"Using a JSON document column or a list of tag ids as the identity of a row; schemas auto-derived from NoSQL sources where the natural 'id' is a composite/structured value.","solutions":["Choose a scalar key column (int, str, date/time, etc.) as the snapshot primary key.","Derive a scalar key from the structured column first, e.g. key = t.payload.apply(lambda d: json.dumps(d, sort_keys=True)) cast to STRING — note DuckDB indexes strings, so a deterministic string encoding works.","Restructure the pipeline so composite identity is hashed into a single scalar column (e.g. with pw.apply) before writing."],"exampleFix":"# before\npw.io.duckdb.write(t, table_name=\"t\", output_table_type=\"snapshot\", primary_key=t.tags)  # tags: list\n\n# after\nimport json\nt = t.with_columns(key=pw.apply(lambda tags: json.dumps(tags, sort_keys=True), t.tags))\npw.io.duckdb.write(t, table_name=\"t\", output_table_type=\"snapshot\", primary_key=t.key)","handlingStrategy":"type-guard","validationCode":"from pathway import dt\nINDEXABLE = (dt.List, dt.Array, dt.Tuple)\nfor pk in primary_key or []:\n    d = pk._column.dtype\n    assert not (isinstance(d, INDEXABLE) or d == dt.JSON), f\"non-scalar key column: {pk.name}\"","typeGuard":"def is_scalar_indexable(dtype) -> bool:\n    import pathway as pw\n    dt = pw.dt\n    return not (isinstance(dtype, (dt.List, dt.Array, dt.Tuple)) or dtype == dt.JSON)","tryCatchPattern":null,"preventionTips":["Always key snapshots on scalar columns; encode structured identity into a deterministic string if needed.","Test the snapshot write path in CI with the real schema types to catch key-type issues early."],"tags":["duckdb","primary-key","schema","type-mismatch"],"backgroundTag":null,"analyzedSha":"fa2f74a4649b7c5908690cf60137263d8d80de5f","analyzedAt":"2026-08-15T01:48:17.006Z","schemaVersion":2},"datasetVersion":"2026-08-15T22:17:37.221Z"}