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

Raised by pw.io.duckdb.write in snapshot mode when a column used in primary_key is declared Optional (nullable) in the Pathway schema. A nullable key makes DELETE ... WHERE pk = NULL never match on retractions, so the destination table would keep stale rows forever; the connector rejects it at write() time.

Source

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

        for pkey in primary_key:
            if pkey.name in names_seen and pkey.name not in duplicates:
                duplicates.append(pkey.name)
            names_seen.add(pkey.name)
        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)
            # A nullable primary key makes DELETE ... WHERE pk = NULL never match
            # on retractions, so the destination would keep stale rows.
            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."
                )
            # DuckDB cannot build a PRIMARY KEY / index on a list, array, tuple or
            # JSON column, so such a column can never serve as a snapshot primary
            # key — the CREATE TABLE (or the upsert's ON CONFLICT) would fail with
            # an opaque "Invalid type for index key" error mid-run.
            if isinstance(pkey._column.dtype, (dt.List, dt.Array, dt.Tuple)) or (
                pkey._column.dtype == dt.JSON
            ):
                raise ValueError(
                    f"primary_key column {pkey.name!r} has non-scalar type "
                    f"({pkey._column.dtype}); DuckDB cannot index list, array, "
                    "tuple or JSON columns, so they cannot be used as a snapshot "
                    "primary key."
                )
            key_field_names.append(pkey.name)

View on GitHub (pinned to fa2f74a464)

Solutions

1. Make the key column required in the schema: class Input(pw.Schema, id: int) instead of id: Optional[int].
2. Pick a different, non-nullable column as the primary key.
3. If the value can be missing, fill it before writing (e.g. .fillna()) and declare the schema column non-optional.

Example fix

# before
class Input(pw.Schema):
    user_id: Optional[int]
pw.io.duckdb.write(t, table_name="t", output_table_type="snapshot", primary_key=t.user_id)

# after
class Input(pw.Schema):
    user_id: int
pw.io.duckdb.write(t, table_name="t", output_table_type="snapshot", primary_key=t.user_id)

from pathway import dt
for pk in primary_key or []:
    assert not isinstance(pk._column.dtype, dt.Optional), f"nullable key column: {pk.name}"

def is_non_nullable_key(column) -> bool:
    return not isinstance(column._column.dtype, __import__('pathway').dt.Optional)

When it happens

Trigger: pw.io.duckdb.write(t, table_name="t", output_table_type="snapshot", primary_key=t.user_id) where the schema declares user_id: Optional[int] or column_definition(secondary_key=True).

Common situations: Schemas generated from JSON/CSV sources where every column is optional by default; using a column that is genuinely sometimes missing as the key.

Related errors

• primary_key column {pkey.name!r} has non-scalar type ({pkey.
• pw.io.mssql.read primary_key column(s) {nullable_pks} are de
• pw.io.mysql.read primary_key column(s) {nullable_pks} are de
• pw.Schema has column names that differ only in case ({case_c
• Column(s) {collisions} collide with the 'time' and 'diff' me

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