pathwaycom/pathway · error · ValueError

primary key field names must be specified for a snapshot mod

Error message

primary key field names must be specified for a snapshot mode

What it means

Raised by pw.io.postgres.write() in snapshot mode when primary_key is None or an empty list. The snapshot writer maintains the destination with INSERT ... ON CONFLICT (...) DO UPDATE, which is malformed SQL without at least one key column; Pathway rejects it before any init_mode side effect (e.g. CREATE TABLE) touches the database.

Source

Thrown at python/pathway/io/postgres/__init__.py:897

            )
        if primary_key is not None:
            raise ValueError(
                "primary_key can only be specified for the snapshot table type"
            )
    else:
        # Snapshot mode requires at least one primary-key column —
        # the writer's INSERT ... ON CONFLICT (...) DO UPDATE
        # statement is malformed without one. If we let an empty list
        # reach the engine it would error out only AFTER ``init_mode``
        # has already mutated the destination (CREATE TABLE for
        # ``"replace"`` / ``"create_if_not_exists"``), and under
        # multi-worker (PATHWAY_THREADS > 1) the worker that loses the
        # CREATE race observes the partially-created table and
        # surfaces a less specific error instead — making any
        # message-based test flaky. Reject at call time so no DB side
        # effect happens.
        if primary_key is None or len(primary_key) == 0:
            raise ValueError(
                "primary key field names must be specified for a snapshot mode"
            )
    if (
        _external_diff_column is not None
        and _external_diff_column._column.dtype != dtype.INT
    ):
        raise ValueError("_external_diff_column can only have an integer type")

    external_diff_column_index = get_column_index(table, _external_diff_column)
    key_field_names = None
    if primary_key is not None:
        key_field_names = [pkey_field.name for pkey_field in primary_key]
        # `primary_key=[other_table.col]` (or a reference whose name
        # simply isn't in `table`) is accepted today but then either
        # generates a malformed CREATE TABLE (``PRIMARY KEY
        # ("unknown")``) on init or produces an UPSERT that panics at
        # flush. Reject up-front with a clear message.
        table_columns = set(table.schema.column_names())

View on GitHub (pinned to fa2f74a464)

Solutions

1. Pass a non-empty key list: primary_key=[table.id].
2. If no natural key exists, add one to the schema (pw.column_definition(primary_key=True)) or synthesize one (e.g. pw.this.apply(...) hash) before writing.
3. If you cannot key the rows, use the default stream_of_changes mode instead.

Example fix

# before
pw.io.postgres.write(t, parts, "tbl", output_table_type="snapshot")
# after
pw.io.postgres.write(t, parts, "tbl", output_table_type="snapshot", primary_key=[t.doc_id])

if output_table_type == "snapshot":
    assert primary_key, "snapshot write needs at least one primary-key column"

When it happens

Trigger: pw.io.postgres.write(table, parts, "tbl", output_table_type="snapshot") with no primary_key, or primary_key=[].

Common situations: Users adding output_table_type="snapshot" for upsert behavior but forgetting the key; tables whose schema has no primary_key column definition to rely on.

Related errors

• Incorrect S3 path: {s3_path}
• primary_key can only be specified for the snapshot table typ
• _external_diff_column can only have an integer type
• primary_key references column(s) {foreign} that are not pres
• primary_key contains duplicate column(s) {duplicates}

AI-assisted analysis of pathwaycom/pathway@fa2f74a464 (2026-08-15). Data as JSON: /api/errors/6360aa97fec8e6ff. Report an issue: GitHub.