pathwaycom/pathway · error · ValueError

pw.Schema has column names that differ only in case

Error message

pw.Schema has column names that differ only in case ({case_collisions}). SQL Server's default collation is case-insensitive, so CREATE TABLE would reject them as duplicates. Rename these columns in the Pathway table so every column name is unique case-insensitively.

What it means

SQL Server's default collation compares identifiers case-insensitively, so `id` and `ID` are the same column name. If a Pathway table's schema contains column names that differ only in case, the CREATE TABLE emitted by pw.io.mssql.write would fail with a raw 'duplicate column name' driver error at pipeline startup. Pathway raises this ValueError at write() time with the colliding groups so the fix is obvious.

Solutions

  1. Rename the colliding columns in the Pathway table before writing, e.g. with table.select(**{"user_id": table.userId, "user_id_upper": table.UserID}) or table.rename().
  2. Standardize casing conventions across the pipeline (PEP 8 snake_case) so collisions cannot arise.
  3. Drop one of the duplicated-case columns before the sink if it is redundant.

Example fix

# before
result = table.select(table.userId, table.UserID)
pw.io.mssql.write(result, "t", output_table_type="snapshot")

# after
result = table.select(user_id=table.userId, user_id_src=table.UserID)
pw.io.mssql.write(result, "t", output_table_type="snapshot")
Defensive patterns

Strategy: validation

Validate before calling

def case_collisions(column_names: list[str]) -> list[list[str]]:
    groups: dict[str, list[str]] = {}
    for n in column_names:
        groups.setdefault(n.lower(), []).append(n)
    return [sorted(g) for g in groups.values() if len(g) > 1]

assert not case_collisions(table.schema.column_names()), "case-insensitive collisions present"

Type guard

def is_case_unique(column_names: list[str]) -> bool:
    lowered = [n.lower() for n in column_names]
    return len(lowered) == len(set(lowered))

Try / catch

try:
    pw.io.mssql.write(table, "t", output_table_type="snapshot")
except ValueError as e:
    if "differ only in case" in str(e):
        raise SystemExit("Rename case-colliding columns before the MSSQL sink") from e
    raise

Prevention

When it happens

Trigger: Writing a table whose schema declares two columns whose lowercased names match, e.g. class S(pw.Schema): userId: int and UserID: str, then calling pw.io.mssql.write(table, ...) in either output mode.

Common situations: Renaming columns with .rename() or .select() and accidentally producing case variants; joining/unioning tables from sources with different casing conventions (userId vs UserId); schemas written by different team members with inconsistent casing.

Related errors

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

Appendix: source

Thrown at python/pathway/io/mssql/__init__.py:403

        raise ValueError(
            "primary_key can only be specified for the snapshot table type"
        )

    value_fields = _format_output_value_fields(table)

    # SQL Server's default collation matches identifiers case-insensitively
    # (`id` and `ID` resolve to the same column), so any pair of schema
    # columns that differ only in case would make CREATE TABLE fail with a
    # raw "duplicate column name" driver error at pipeline-startup.  Surface
    # the collision here with a Pathway-authored message instead.
    case_groups: dict[str, list[str]] = {}
    for field in value_fields:
        case_groups.setdefault(field.name.lower(), []).append(field.name)
    case_collisions = [
        sorted(names) for names in case_groups.values() if len(names) > 1
    ]
    if case_collisions:
        raise ValueError(
            f"pw.Schema has column names that differ only in case "
            f"({case_collisions}). SQL Server's default collation is "
            "case-insensitive, so CREATE TABLE would reject them as "
            "duplicates. Rename these columns in the Pathway table so "
            "every column name is unique case-insensitively."
        )

    if not is_snapshot_mode:
        # Stream-of-changes mode appends `[time]` / `[diff]` metadata columns
        # to the destination table.  If the user's own schema already has a
        # column with one of those names, the generated CREATE TABLE would
        # declare it twice and SQL Server would reject it with an opaque
        # "duplicate column name" error at startup.  Comparison is
        # case-insensitive — SQL Server's default collation treats `Time` and
        # `time` as the same identifier.
        reserved_metadata_columns = {"time", "diff"}
        collisions = sorted(
            field.name

View on GitHub (pinned to fa2f74a464)