{"record":{"id":"1d0f2282883e65bb","repo":"pathwaycom/pathway","slug":"primary-key-can-only-be-specified-for-the-snapshot-1d0f22","errorCode":null,"errorMessage":"primary_key can only be specified for the snapshot table type","messagePattern":"primary_key can only be specified for the snapshot table type","errorType":"exception","errorClass":"ValueError","httpStatus":null,"severity":"error","filePath":"python/pathway/io/sqlite/__init__.py","lineNumber":299,"sourceCode":"    key and every ``-1`` event issues a DELETE against the matching row.\n    A primary key must be supplied via ``primary_key``:\n\n    >>> pw.io.sqlite.write(\n    ...     t,\n    ...     \"pets.db\",\n    ...     \"pets_snapshot\",\n    ...     output_table_type=\"snapshot\",\n    ...     primary_key=[t.owner, t.pet],\n    ...     init_mode=\"replace\",\n    ... )\n\n    Here ``(owner, pet)`` is the primary key, so at any point in time the\n    ``pets_snapshot`` table contains one row per live ``(owner, pet)``\n    pair — no history, no ``time`` / ``diff`` columns.\n    \"\"\"\n    is_snapshot_mode = output_table_type == SNAPSHOT_OUTPUT_TABLE_TYPE\n    if not is_snapshot_mode and primary_key is not None:\n        raise ValueError(\n            \"primary_key can only be specified for the snapshot table type\"\n        )\n    if is_snapshot_mode and not primary_key:\n        raise ValueError(\"primary_key must be specified for the snapshot table type\")\n\n    path_str = fspath(path)\n    _reject_directory_path(path_str)\n\n    value_fields = _format_output_value_fields(table)\n\n    # SQLite identifier matching is case-insensitive (`ID` and `id` are\n    # the same column), so any pair of schema columns whose names\n    # differ only in case would make ``CREATE TABLE`` fail with a raw\n    # ``duplicate column name`` driver error at pipeline-start. Surface\n    # the collision here with a clear, Pathway-authored message\n    # instead, matching how the ``time`` / ``diff`` reserved-name check\n    # below works.\n    case_groups: dict[str, list[str]] = {}","sourceCodeStart":281,"sourceCodeEnd":317,"githubUrl":"https://github.com/pathwaycom/pathway/blob/fa2f74a4649b7c5908690cf60137263d8d80de5f/python/pathway/io/sqlite/__init__.py#L281-L317","documentation":"Pathway's SQLite output connector accepts a `primary_key` argument only in snapshot mode (output_table_type=\"snapshot\"), where it drives the INSERT ... ON CONFLICT UPSERT and DELETE statements. In the default stream_of_changes mode the connector appends `time`/`diff` metadata columns and appends every change, so a primary key has no meaning. Passing primary_key together with the default output_table_type therefore raises this ValueError at write() call time, before any data flows.","triggerScenarios":"Calling pw.io.sqlite.write(table, path, table_name, primary_key=[...]) without also passing output_table_type=\"snapshot\". Any primary_key value (list of column references or a single reference) combined with output_table_type=\"stream_of_changes\" (the default) triggers it.","commonSituations":"A developer copies the snapshot example from the docs but deletes or forgets the output_table_type=\"snapshot\" line; or upgrades code that previously used a primary-key-like dedup pattern and assumes the connector infers snapshot mode from primary_key.","solutions":["Add output_table_type=\"snapshot\" to the pw.io.sqlite.write() call if you want keyed upsert/delete semantics.","Remove the primary_key argument if you actually want the default append-only change log with time/diff columns."],"exampleFix":"// before\npw.io.sqlite.write(t, \"pets.db\", \"pets\", primary_key=[t.owner, t.pet])\n\n// after\npw.io.sqlite.write(\n    t, \"pets.db\", \"pets_snapshot\",\n    output_table_type=\"snapshot\",\n    primary_key=[t.owner, t.pet],\n)","handlingStrategy":"validation","validationCode":"def validate_sqlite_write_args(output_table_type, primary_key):\n    snapshot = output_table_type == \"snapshot\"\n    if primary_key is not None and not snapshot:\n        raise ValueError(\"primary_key requires output_table_type='snapshot'\")\n    return True","typeGuard":null,"tryCatchPattern":"try:\n    pw.io.sqlite.write(t, path, name, primary_key=keys)\nexcept ValueError as e:\n    if \"primary_key can only be specified\" in str(e):\n        # retry in snapshot mode or drop the key\n        pw.io.sqlite.write(t, path, name, output_table_type=\"snapshot\", primary_key=keys)\n    else:\n        raise","preventionTips":["Treat output_table_type and primary_key as a paired setting: always set them together for snapshot mode.","Wrap connector construction in a small helper that only exposes a snapshot(path, table_name, keys) signature so the invalid combination cannot be expressed."],"tags":["pathway","sqlite","output-connector","configuration"],"backgroundTag":null,"analyzedSha":"fa2f74a4649b7c5908690cf60137263d8d80de5f","analyzedAt":"2026-08-15T01:48:17.006Z","schemaVersion":2},"datasetVersion":"2026-08-15T22:17:37.221Z"}