pathwaycom/pathway · error · ValueError

primary_key column {pkey.name!r} is declared nullable ({pkey

Error message

primary_key column {pkey.name!r} is declared nullable ({pkey._column.dtype}); primary-key columns must be non-nullable in snapshot mode.

What it means

Snapshot mode turns Pathway retractions into DELETE ... WHERE pk = ?, so a NULL primary-key value either never matches (plain PK types: NULL = NULL is not true in SQL) or, for SQLite INTEGER PRIMARY KEY, silently auto-assigns a rowid that can collide with a later UPSERT and overwrite an unrelated row. Because either behavior silently corrupts the destination table, write() rejects primary-key columns whose dtype is Optional (nullable) at call time.

Source

Thrown at python/pathway/io/sqlite/__init__.py:387

        if duplicates:
            raise ValueError(
                f"primary_key contains duplicate column(s) {sorted(duplicates)}. "
                "Each column may appear at most once."
            )
        key_field_names = []
        for pkey in primary_key:
            # Raises ValueError when `pkey` belongs to a different table
            # or does not name a column of `table`, so users get a clear
            # message at write() time instead of an opaque runtime error.
            get_column_index(table, pkey)
            # Reject nullable primary-key columns. SQLite's
            # INTEGER PRIMARY KEY auto-assigns a rowid for NULL values,
            # which can silently collide with a later UPSERT and
            # overwrite unrelated rows; other PK types let NULLs through
            # but then DELETE ... WHERE pk = NULL never matches on
            # retractions. Neither case is what the user asked for.
            if isinstance(pkey._column.dtype, dt.Optional):
                raise ValueError(
                    f"primary_key column {pkey.name!r} is declared nullable "
                    f"({pkey._column.dtype}); primary-key columns must be "
                    "non-nullable in snapshot mode."
                )
            key_field_names.append(pkey.name)

    data_storage = api.DataStorage(
        storage_type="sqlite",
        path=path_str,
        table_name=table_name,
        table_writer_init_mode=init_mode_from_str(init_mode),
        max_batch_size=max_batch_size,
        snapshot_maintenance_on_output=is_snapshot_mode,
    )
    data_format = api.DataFormat(
        format_type="identity",
        key_field_names=key_field_names,
        value_fields=value_fields,

View on GitHub (pinned to fa2f74a464)

Solutions

1. Make the key column non-nullable in the schema (change `id: int | None` to `id: int`) if the data is in fact always present.
2. Filter out rows with null keys before writing: t = t.filter(t.id.is_not_none()), then use primary_key=[t.id].
3. Choose a different primary-key column that is guaranteed non-null (e.g. an explicit pw.this.id surrogate key).

Example fix

# before
class Input(pw.Schema):
    owner: str | None = None
    pet: str
pw.io.sqlite.write(t, "db", "s", output_table_type="snapshot", primary_key=[t.owner, t.pet])

# after
t = t.filter(pw.this.owner.is_not_none())
pw.io.sqlite.write(t, "db", "s", output_table_type="snapshot", primary_key=[t.owner, t.pet])

import pathway as pw
from pathway import dt

def check_pk_not_nullable(table, primary_key):
    schema = table.schema
    for col in primary_key:
        dtype = schema[col.name].dtype
        if isinstance(dtype, dt.Optional):
            raise ValueError(f"primary key column {col.name} is nullable")
    return True

When it happens

Trigger: Calling pw.io.sqlite.write(..., output_table_type="snapshot", primary_key=[...]) where any referenced column is typed Optional[T] — e.g. a schema field declared as `id: int | None` or made nullable via table.with_columns(id=pw.this.id + 0, id=pw.apply(optional_fn)) — i.e. dtype is dt.Optional.

Common situations: Schemas inferred from JSON/CSV where the key field is sometimes missing; Python-style annotations like `user_id: int | None = None` in a pw.Schema; upstream transformations that introduce nullability on the key column.

Related errors

• pw.io.mssql.read primary_key column(s) {nullable_pks} are de
• primary_key column {pkey.name!r} is declared nullable ({pkey
• pw.io.mysql.read primary_key column(s) {nullable_pks} are de
• primary_key column {name!r} is nullable (type {dtype}); a Pi
• primary_key column '{pkey_field.name}' is declared nullable;

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