pathwaycom/pathway · error · ValueError

primary_key column {pkey.name!r} has non-scalar type ({pkey.

Error message

primary_key column {pkey.name!r} has non-scalar type ({pkey._column.dtype}); DuckDB cannot index list, array, tuple or JSON columns, so they cannot be used as a snapshot primary key.

What it means

Raised by pw.io.duckdb.write when a primary_key column has a non-scalar type (list, array, tuple, or JSON). DuckDB cannot build a PRIMARY KEY or index on those types, so CREATE TABLE or the upsert's ON CONFLICT would fail mid-run with an opaque 'Invalid type for index key' error; the connector rejects it up front.

Source

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

            # 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)

    data_storage = api.DataStorage(
        storage_type="duckdb",
        path=database_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,
        detach_between_batches=detach_between_batches,
    )
    data_format = api.DataFormat(
        format_type="identity",

View on GitHub (pinned to fa2f74a464)

Solutions

1. Choose a scalar key column (int, str, date/time, etc.) as the snapshot primary key.
2. Derive a scalar key from the structured column first, e.g. key = t.payload.apply(lambda d: json.dumps(d, sort_keys=True)) cast to STRING — note DuckDB indexes strings, so a deterministic string encoding works.
3. Restructure the pipeline so composite identity is hashed into a single scalar column (e.g. with pw.apply) before writing.

Example fix

# before
pw.io.duckdb.write(t, table_name="t", output_table_type="snapshot", primary_key=t.tags)  # tags: list

# after
import json
t = t.with_columns(key=pw.apply(lambda tags: json.dumps(tags, sort_keys=True), t.tags))
pw.io.duckdb.write(t, table_name="t", output_table_type="snapshot", primary_key=t.key)

from pathway import dt
INDEXABLE = (dt.List, dt.Array, dt.Tuple)
for pk in primary_key or []:
    d = pk._column.dtype
    assert not (isinstance(d, INDEXABLE) or d == dt.JSON), f"non-scalar key column: {pk.name}"

def is_scalar_indexable(dtype) -> bool:
    import pathway as pw
    dt = pw.dt
    return not (isinstance(dtype, (dt.List, dt.Array, dt.Tuple)) or dtype == dt.JSON)

When it happens

Trigger: pw.io.duckdb.write(t, table_name="t", output_table_type="snapshot", primary_key=t.payload) where payload is dt.List(...), dt.Array, a tuple/JSON column, or a string column typed as dt.JSON.

Common situations: Using a JSON document column or a list of tag ids as the identity of a row; schemas auto-derived from NoSQL sources where the natural 'id' is a composite/structured value.

Related errors

• primary_key column {pkey.name!r} is declared nullable ({pkey
• pw.Schema has column names that differ only in case ({case_c
• Column(s) {collisions} collide with the 'time' and 'diff' me
• primary_key contains duplicate column(s) {sorted(duplicates)
• Defining a primary key in the schema is not supported for pw

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