duckdb-board-varchar-array-columns

Answer

wojons-mythos's tasks table mixes column types. Per DESCRIBE tasks:

DuckDB cannot cast a scalar integer literal to an array type, so a completion-script UPDATE such as

con.execute("UPDATE tasks SET files_changed = 7 WHERE id = ?", [tid])

raises Conversion Error: Unimplemented type for cast (INTEGER -> VARCHAR[]) — even when the value is passed as a parameter, because a Python int binds to INTEGER and then the implicit cast to VARCHAR[] is unimplemented.

Fix: bind array-typed columns as a Python list of strings.

def complete_task(con, task_id: int, files_changed: list[str],
                  lines_added: int, lines_removed: int) -> None:
    con.execute(
        """
        UPDATE tasks
           SET files_changed = ?,
               lines_added   = ?,
               lines_removed = ?
         WHERE id = ?
        """,
        [
            [str(f) for f in files_changed],  # VARCHAR[] -> Python list of str
            lines_added,                      # INTEGER -> Python int
            lines_removed,                    # INTEGER -> Python int
            task_id,
        ],
    )

Key rules for the completion script:

1. VARCHAR[] columns (depends_on, files_changed) → bind a Python list, elements as str. ["7"], not 7.
2. INTEGER columns (lines_added, lines_removed) → bind plain Python ints. No conversion needed.
3. Never write raw integer literals for array columns — files_changed = 7 fails at prepare time; parameter binding does not rescue an int either, because the bind type is still INTEGER.
4. Always run DESCRIBE tasks (or SELECT column_name, data_type FROM information_schema.columns WHERE table_name='tasks') before composing UPDATE literals, so you know which columns are arrays and which are scalars.
5. (Optional) If you prefer SQL text over parameters, the array literal syntax files_changed = ['7'] also works — but parameter binding is the robust choice for values derived from tool output (quotes, commas, etc. are handled automatically).