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.duckdb.write when a `primary_key` argument is supplied while `output_table_type` is not "snapshot" (i.e. the default stream_of_changes mode). A primary key only makes sense for an upsert-style snapshot table; the change-stream output appends time/diff metadata rows and has no keyed state.

Source

Thrown at python/pathway/io/duckdb/__init__.py:252

    ...     init_mode="create_if_not_exists",
    ... )  # doctest: +SKIP
    >>> pw.run()  # doctest: +SKIP

    Afterwards the embeddings can be searched with plain DuckDB SQL:

    .. code-block:: sql

        SELECT text, list_cosine_similarity(embedding, [1.0, 0.0, 0.0]) AS score
        FROM documents
        WHERE diff = 1
        ORDER BY score DESC
        LIMIT 5;
    """
    _check_entitlements("duckdb")

    is_snapshot_mode = output_table_type == SNAPSHOT_OUTPUT_TABLE_TYPE
    if not is_snapshot_mode and primary_key is not None:
        raise ValueError(
            "primary_key can only be specified for the snapshot table type"
        )
    if is_snapshot_mode and not primary_key:
        raise ValueError("primary_key must be specified for the snapshot table type")
    if is_snapshot_mode and sort_by is not None:
        # In snapshot mode an update is a deletion (-1) followed by an insertion
        # (+1) of the same key. sort_by reorders the changes within a minibatch
        # and can place the insertion before the deletion, so the upsert is
        # immediately wiped by the delete — silently losing rows. sort_by has no
        # effect on a snapshot's final (unordered, keyed) state anyway, so reject
        # the combination instead of corrupting the output.
        raise ValueError(
            "sort_by cannot be used with the snapshot table type: a snapshot "
            "reflects the current state of the table regardless of the order "
            "changes are applied within a minibatch, and reordering would corrupt "
            "the upsert/delete sequence. Remove sort_by, or use "
            'output_table_type="stream_of_changes".'
        )

View on GitHub (pinned to fa2f74a464)

Solutions

1. Set output_table_type="snapshot" so primary_key drives the upsert behavior: pw.io.duckdb.write(t, table_name="t", output_table_type="snapshot", primary_key=t.id).
2. Or drop the primary_key argument if you actually want an append-only stream of changes with time/diff columns.

Example fix

# before
pw.io.duckdb.write(t, table_name="t", primary_key=t.id)

# after
pw.io.duckdb.write(t, table_name="t", output_table_type="snapshot", primary_key=t.id)

if primary_key is not None and output_table_type != "snapshot":
    raise ValueError("primary_key requires output_table_type='snapshot'")

When it happens

Trigger: pw.io.duckdb.write(table, table_name="t", primary_key=table.some_col) — primary_key given but output_table_type left at its default, or explicitly set to "stream_of_changes".

Common situations: Upgrading or copying code from other connectors (e.g. pw.io.postgres) where primary_key is accepted for deduplication; assuming snapshot semantics without reading the default output_table_type.

Related errors

• primary_key must be specified for the snapshot table type
• primary_key contains duplicate column(s) {sorted(duplicates)
• database {path_str!r} is an existing directory, not a DuckDB
• sort_by cannot be used with the snapshot table type: a snaps
• detach_between_batches=True cannot be used with database=":m

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