pathwaycom/pathway · error · ValueError

primary_key can only be specified for the snapshot table typ

Error message

primary_key can only be specified for the snapshot table type

What it means

Raised by pw.io.postgres.write() when primary_key is passed while output_table_type is not "snapshot". Keys and UPSERT semantics exist only in snapshot mode; the default stream_of_changes mode appends every change, so a key would be meaningless and is rejected.

Source

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

    data_storage = api.DataStorage(
        storage_type="postgres",
        connection_string=_connection_string_from_settings(postgres_settings),
        max_batch_size=max_batch_size,
        table_name=table_name,
        schema_name=schema_name,
        table_writer_init_mode=init_mode_from_str(init_mode),
        snapshot_maintenance_on_output=is_snapshot_mode,
        tls_settings=tls.settings,
    )

    if not is_snapshot_mode:
        if _external_diff_column is not None:
            raise ValueError(
                "_external_diff_column is only supported for the snapshot table type"
            )
        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"
            )

View on GitHub (pinned to fa2f74a464)

Solutions

1. Add output_table_type="snapshot" to the call.
2. If you truly want append-only history, remove primary_key (the time/diff columns encode changes).

Example fix

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

if primary_key is not None:
    assert output_table_type == "snapshot", "primary_key requires output_table_type='snapshot'"

When it happens

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

Common situations: Users expecting an ON CONFLICT upsert by default; copy-pasting a snapshot example call without the output_table_type line.

Related errors

• _external_diff_column is only supported for the snapshot tab
• primary key field names must be specified for a snapshot mod
• primary_key references column(s) {foreign} that are not pres
• primary_key column '{pkey_field.name}' is declared nullable;
• Incorrect S3 path: {s3_path}

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