{"record":{"id":"d1ba2659eca43b80","repo":"pathwaycom/pathway","slug":"column-s-collisions-collide-with-the-time-and-d1ba26","errorCode":null,"errorMessage":"Column(s) {collisions} collide with the 'time' and 'diff' metadata columns appended in stream_of_changes mode. Rename these columns in the Pathway table, or use output_table_type=\"snapshot\".","messagePattern":"Column\\(s\\) (.+?) collide with the 'time' and 'diff' metadata columns appended in stream_of_changes mode\\. Rename these columns in the Pathway table, or use output_table_type=\"snapshot\"\\.","errorType":"exception","errorClass":"ValueError","httpStatus":null,"severity":"error","filePath":"python/pathway/io/sqlite/__init__.py","lineNumber":349,"sourceCode":"\n    if not is_snapshot_mode:\n        # Stream-of-changes mode appends `time` / `diff` metadata columns\n        # to the destination table so the output can be replayed as a\n        # change log. If the user's own schema already has a column with\n        # one of those names, the generated CREATE TABLE would declare\n        # that column twice and SQLite would reject it. Catch this at\n        # write() time with a clear message instead of letting the user\n        # hit an opaque \"duplicate column name\" error at start-up.\n        # Matching is case-insensitive, consistent with SQLite's identifier\n        # comparison rules.\n        reserved_metadata_columns = {\"time\", \"diff\"}\n        collisions = sorted(\n            field.name\n            for field in value_fields\n            if field.name.lower() in reserved_metadata_columns\n        )\n        if collisions:\n            raise ValueError(\n                f\"Column(s) {collisions} collide with the 'time' and 'diff' \"\n                \"metadata columns appended in stream_of_changes mode. Rename \"\n                \"these columns in the Pathway table, or use \"\n                'output_table_type=\"snapshot\".'\n            )\n\n    key_field_names: list[str] | None = None\n    if primary_key is not None:\n        # Duplicate entries in `primary_key` produce a nonsensical SQL\n        # template — e.g. `PRIMARY KEY (\"k\", \"k\")` — which SQLite tolerates,\n        # but the shared `SqlQueryTemplate` then reorders DELETE bindings\n        # using the duplicated index and retractions silently match no\n        # rows. Reject here with a clear message.\n        names_seen: set[str] = set()\n        duplicates: list[str] = []\n        for pkey in primary_key:\n            if pkey.name in names_seen and pkey.name not in duplicates:\n                duplicates.append(pkey.name)","sourceCodeStart":331,"sourceCodeEnd":367,"githubUrl":"https://github.com/pathwaycom/pathway/blob/fa2f74a4649b7c5908690cf60137263d8d80de5f/python/pathway/io/sqlite/__init__.py#L331-L367","documentation":"In the default stream_of_changes mode the SQLite connector appends two INTEGER metadata columns, `time` and `diff`, to the destination table so the output can be replayed as a change log. If the Pathway table's own schema already contains a column named time or diff (matching is case-insensitive, consistent with SQLite identifier rules), the generated CREATE TABLE would declare that column twice and SQLite would reject it. write() detects the collision and raises this ValueError at call time instead of an opaque duplicate-column failure at pipeline start.","triggerScenarios":"Calling pw.io.sqlite.write(table, ...) with output_table_type left at the default \"stream_of_changes\" (or passed explicitly) on a table that has a column named time, diff, TIME, Diff, etc.","commonSituations":"Event/log tables that naturally carry a `time` or `diff` column; porting a pipeline from another sink (e.g. Postgres or CSV output) that allowed those names; snapshot-mode examples that were switched back to change-log mode without renaming.","solutions":["Rename the offending column in the Pathway table before writing, e.g. t.select(**{**{c: c for c in t.schema.columns()}, 'time': 'event_time'}) or table.rename_columns().","Switch to output_table_type=\"snapshot\" (with a required primary_key), which does not append time/diff columns."],"exampleFix":"# before\npw.io.sqlite.write(t_with_time_col, \"db.sqlite\", \"events\")\n\n# after\nt = t_with_time_col.rename_columns(event_time=pw.this.time)\npw.io.sqlite.write(t, \"db.sqlite\", \"events\")","handlingStrategy":"validation","validationCode":"RESERVED = {'time', 'diff'}\ndef check_reserved_columns(table, output_table_type=\"stream_of_changes\"):\n    if output_table_type != \"stream_of_changes\":\n        return True\n    hits = [c for c in table.schema.column_names() if c.lower() in RESERVED]\n    if hits:\n        raise ValueError(f\"columns {hits} collide with time/diff metadata; rename them\")\n    return True","typeGuard":null,"tryCatchPattern":null,"preventionTips":["Avoid naming data columns `time` or `diff` in schemas destined for the SQLite change-log sink; prefer event_time/change.","Add a schema lint step in CI that flags reserved sink-specific column names per connector."],"tags":["pathway","sqlite","schema","naming-conflict"],"backgroundTag":null,"analyzedSha":"fa2f74a4649b7c5908690cf60137263d8d80de5f","analyzedAt":"2026-08-15T01:48:17.006Z","schemaVersion":2},"datasetVersion":"2026-08-15T22:17:37.221Z"}