{"record":{"id":"9b6151a0cc39f418","repo":"pathwaycom/pathway","slug":"external-diff-column-can-only-have-an-integer-typ","errorCode":null,"errorMessage":"_external_diff_column can only have an integer type","messagePattern":"_external_diff_column can only have an integer type","errorType":"exception","errorClass":"ValueError","httpStatus":null,"severity":"error","filePath":"python/pathway/io/postgres/__init__.py","lineNumber":904,"sourceCode":"        # the writer's INSERT ... ON CONFLICT (...) DO UPDATE\n        # statement is malformed without one. If we let an empty list\n        # reach the engine it would error out only AFTER ``init_mode``\n        # has already mutated the destination (CREATE TABLE for\n        # ``\"replace\"`` / ``\"create_if_not_exists\"``), and under\n        # multi-worker (PATHWAY_THREADS > 1) the worker that loses the\n        # CREATE race observes the partially-created table and\n        # surfaces a less specific error instead — making any\n        # message-based test flaky. Reject at call time so no DB side\n        # effect happens.\n        if primary_key is None or len(primary_key) == 0:\n            raise ValueError(\n                \"primary key field names must be specified for a snapshot mode\"\n            )\n    if (\n        _external_diff_column is not None\n        and _external_diff_column._column.dtype != dtype.INT\n    ):\n        raise ValueError(\"_external_diff_column can only have an integer type\")\n\n    external_diff_column_index = get_column_index(table, _external_diff_column)\n    key_field_names = None\n    if primary_key is not None:\n        key_field_names = [pkey_field.name for pkey_field in primary_key]\n        # `primary_key=[other_table.col]` (or a reference whose name\n        # simply isn't in `table`) is accepted today but then either\n        # generates a malformed CREATE TABLE (``PRIMARY KEY\n        # (\"unknown\")``) on init or produces an UPSERT that panics at\n        # flush. Reject up-front with a clear message.\n        table_columns = set(table.schema.column_names())\n        foreign = sorted(n for n in key_field_names if n not in table_columns)\n        if foreign:\n            raise ValueError(\n                f\"primary_key references column(s) {foreign} that are \"\n                \"not present in the written table; pass ColumnReferences \"\n                \"from the table being written, e.g. primary_key=[table.k].\"\n            )","sourceCodeStart":886,"sourceCodeEnd":922,"githubUrl":"https://github.com/pathwaycom/pathway/blob/fa2f74a4649b7c5908690cf60137263d8d80de5f/python/pathway/io/postgres/__init__.py#L886-L922","documentation":"Raised by pw.io.postgres.write() when _external_diff_column is provided but its dtype is not Pathway's INT. The engine side expects a native integer (+1/-1) for the change flag, so float, string, or pointer-typed columns are rejected at call time.","triggerScenarios":"pw.io.postgres.write(t, parts, \"tbl\", output_table_type=\"snapshot\", primary_key=[t.k], _external_diff_column=t.d) where t.d is float, str, or bool typed in the schema.","commonSituations":"Diff columns produced by UDFs that return float(1)/float(-1) or Python objects; schemas inferred from pandas/Arrow where integers become int64-but-nullable or float columns.","solutions":["Cast the column to int before the write: t.with_columns(d=pw.this.d.astype(int)).","Or produce it as int from the start (annotate the schema field or the UDF return type as int)."],"exampleFix":"# before\n@pw.udf\ndef diff(x: float) -> float:\n    return 1.0 if x > 0 else -1.0\n# after\n@pw.udf\ndef diff(x: float) -> int:\n    return 1 if x > 0 else -1","handlingStrategy":"type-guard","validationCode":"import pathway as pw\nif _external_diff_column is not None:\n    assert _external_diff_column._column.dtype == pw.dtype.INT, \"diff column must be INT\"","typeGuard":"def is_int_column(col) -> bool:\n    import pathway as pw\n    return col is not None and col._column.dtype == pw.dtype.INT","tryCatchPattern":null,"preventionTips":["Type diff-producing UDFs with int return annotations so Pathway infers INT.","Check column dtypes in a schema smoke test before wiring connectors."],"tags":["postgres","pathway","dtype","snapshot","validation"],"backgroundTag":null,"analyzedSha":"fa2f74a4649b7c5908690cf60137263d8d80de5f","analyzedAt":"2026-08-15T01:48:17.006Z","schemaVersion":2},"datasetVersion":"2026-08-15T22:17:37.221Z"}