fix/duckdb/failed-to-cast-value
DuckDB error

Failed to cast value

Updated Aug 27, 20266-min read
TL;DR

DuckDB tried to convert a value from one type to another and the value did not fit. Almost always because an explicit CAST hit a bad row, a CSV or Parquet reader inferred a narrower type from its sample than the full data actually needs, or a Python DataFrame column carried a dtype DuckDB does not recognize.

  • ·An explicit CAST or ::type is running against a column that contains a value the target type cannot hold
  • ·read_csv or read_parquet sampled the first N rows, guessed a narrow type (INTEGER, DECIMAL(11,8), DATE), and later rows overflow it
  • ·You are reading JSON and a field that looks numeric contains one non-numeric value
  • ·A pandas DataFrame column is dtype object or a pyarrow-backed string that DuckDB cannot map
  • ·The value is out of range for the target (a DECIMAL(11,8) cannot hold 1286.82000000, an INTEGER cannot hold 3_000_000_000)
CHECK FIRSTRead the full error line. DuckDB names the source type, the target type, and usually the exact value that failed. That tells you whether to widen the target type, clean the source, or swap CAST for TRY_CAST.

What you're seeing

The query aborts with a Conversion Error or an Invalid Input Error. The wording varies slightly by client and by whether the cast is explicit or inferred, but the substance is identical.

text
Conversion Error: Failed to cast value: Could not convert string 'abc' to INT32
Invalid Input Error: Failed to cast value: Casting value "1286.82000000" to type DECIMAL(11,8) failed: value is out of range!
Conversion Error: Failed to cast value to numerical

# Python client, related type-inference failures:
duckdb.NotImplementedException: Not implemented Error: Data type 'str' not recognized
duckdb.NotImplementedException: Not implemented Error: Data type 'int64[pyarrow]' not recognized
Also seen as: duckdb failed to cast value, duckdb conversion error, duckdb.ConversionException, Failed to cast value to numerical, Casting value to type DECIMAL failed, don't know what type, Data type not recognized

What's causing this

Ranked most-likely first.

  1. 1

    The value does not fit the target type

    The most literal case. CAST('abc' AS INTEGER) fails because 'abc' is not a number. CAST(3000000000 AS INTEGER) fails because it overflows a signed 32-bit INTEGER. CAST(1286.82 AS DECIMAL(11,8)) fails because the integer part needs more than 3 digits.

  2. 2

    CSV or Parquet auto-detection sampled too little of the file

    read_csv reads a sample (default 20480 rows) to infer column types. If every sampled value is a small integer but a later row holds NULL, a decimal, or a string, the scan fails partway through with a cast error. The same happens for read_parquet when the schema in the footer does not match the values, or when reading multiple Parquet files with divergent schemas.

  3. 3

    A pandas DataFrame column has a dtype DuckDB cannot map

    DuckDB's Python replacement scan handles standard NumPy dtypes and some pyarrow types, but pandas 2 defaults like string[pyarrow], int64[pyarrow], or timedelta64[us] surface as 'Data type X not recognized'. Object columns with mixed content surface as cast failures during the scan.

  4. 4

    Implicit cast in a WHERE clause or JOIN key

    Comparing a VARCHAR column to an INTEGER literal, or joining on columns of different types, forces DuckDB to cast one side. If any row in the VARCHAR column is non-numeric, the whole query fails even though most rows would compare fine.

  5. 5

    JSON extraction returns a string that will not parse as a number

    json_extract and the ->> operator return VARCHAR by default. Wrapping that in an integer cast fails as soon as one row has a null, empty string, or non-numeric value. This is common when a field is nullable or was renamed upstream.

  6. 6

    Reading multiple files with a UNION_BY_NAME schema mismatch

    read_parquet('files/*.parquet', union_by_name = true) and the equivalent read_csv path unify columns across files. If the same column has INTEGER in one file and VARCHAR in another, DuckDB casts to the wider type, and a stray value in the narrower file blows up the cast.

How to fix it

Step 1: read the error to find the source type, target type, and value

DuckDB's cast errors are specific. They tell you which value failed and what type it was going to. Copy the failing value into a scratch query and confirm the cast on its own before touching the real query.

sql
-- Reproduce the failing cast in isolation:
SELECT CAST('1286.82000000' AS DECIMAL(11,8));
-- Invalid Input Error: value is out of range!

SELECT CAST('abc' AS INTEGER);
-- Conversion Error: Could not convert string 'abc' to INT32

Step 2: swap CAST for TRY_CAST when a bad row is acceptable

TRY_CAST returns NULL on failure instead of aborting the query. Use it when a few unparseable rows should not kill the whole scan, or when you want to filter them out downstream with a WHERE result IS NOT NULL.

sql
-- Instead of:
SELECT CAST(user_id AS INTEGER) FROM events;

-- Use:
SELECT TRY_CAST(user_id AS INTEGER) AS user_id FROM events;

-- Or filter the bad rows out:
SELECT user_id
FROM events
WHERE TRY_CAST(user_id AS INTEGER) IS NOT NULL;

Step 3: widen the target type

If DECIMAL(11,8) rejects your value, use DECIMAL(18,8). If INTEGER overflows, use BIGINT. HUGEINT covers 128-bit values. For strings, VARCHAR has no length limit in DuckDB so use it freely.

sql
-- DECIMAL(precision, scale): precision is total digits, scale is digits after the point.
-- 1286.82000000 needs 4 integer digits + 8 fractional = DECIMAL(12,8) minimum.
SELECT CAST('1286.82000000' AS DECIMAL(18,8));

-- For integers that outgrow INT32:
SELECT CAST(user_id AS BIGINT) FROM events;

Step 4: give read_csv the types explicitly instead of trusting the sample

The cheapest fix for CSV cast errors is to name the columns you care about and their types. Pass types as a map. For a one-off exploratory read, sample_size = -1 forces DuckDB to scan the whole file for inference, at the cost of a slower initial read.

sql
-- Name the columns whose type you know:
SELECT * FROM read_csv('events.csv',
  types = {'user_id': 'BIGINT', 'amount': 'DECIMAL(18,4)', 'ts': 'TIMESTAMP'}
);

-- Or scan the whole file to infer types safely:
SELECT * FROM read_csv('events.csv', sample_size = -1);

-- Or read everything as VARCHAR and cast in SQL where you control the rules:
SELECT * FROM read_csv('events.csv', all_varchar = true);

Step 5: for read_parquet, unify schemas or read files separately

When multiple Parquet files have divergent column types, union_by_name forces a cast that can fail. Either cast the source files to a common schema in a prior step, or read them one at a time and UNION the results with explicit casts.

sql
-- Instead of trusting union_by_name to reconcile INTEGER vs VARCHAR:
SELECT * FROM read_parquet('files/*.parquet', union_by_name = true);

-- Read each file and cast explicitly:
SELECT CAST(user_id AS BIGINT) AS user_id, amount
FROM read_parquet('files/2026-01.parquet')
UNION ALL
SELECT TRY_CAST(user_id AS BIGINT) AS user_id, amount
FROM read_parquet('files/2026-02.parquet');

Step 6: clean pandas DataFrame dtypes before handing them to DuckDB

If you hit 'Data type X not recognized' or a scan-time cast error on a DataFrame, the offending column is usually a pyarrow-backed dtype or an object column with mixed content. Convert to a NumPy dtype DuckDB knows how to read, or pass through an Arrow table.

python
import duckdb
import pandas as pd

# If a column is string[pyarrow] or int64[pyarrow]:
df["name"] = df["name"].astype("object")
df["user_id"] = df["user_id"].astype("int64")

# Or route via Arrow, which DuckDB reads directly:
import pyarrow as pa
tbl = pa.Table.from_pandas(df)
duckdb.sql("SELECT * FROM tbl").fetchall()

Step 7: for JSON extraction, wrap the cast in TRY_CAST

json_extract returns strings. Nullable or optional JSON fields blow up hard casts. TRY_CAST turns the failure into a NULL, which is usually what you meant.

sql
-- Fails as soon as one row's price is missing or non-numeric:
SELECT CAST(payload->>'price' AS DECIMAL(18,2)) FROM events;

-- Safe version:
SELECT TRY_CAST(payload->>'price' AS DECIMAL(18,2)) AS price FROM events;

Prevention

Prefer TRY_CAST over CAST in any pipeline that ingests data you did not write yourself. The performance difference is negligible and the query no longer aborts on a single bad row. Filter or count the NULLs downstream to catch quality issues.

When ingesting CSV or Parquet on a schedule, pin the column types with the types option instead of relying on auto-detection. Type inference is convenient for exploration and dangerous for production because the sample can lie about what the full file contains.

For pandas workflows, keep DataFrame columns on standard NumPy dtypes when the destination is DuckDB. If your pandas 2 setup infers pyarrow-backed strings by default, either turn that off for the DataFrames DuckDB will read, or convert through pyarrow.Table which DuckDB handles cleanly.

Debug DuckDB faster

1bench is a native GUI for DuckDB. Inspect queries, connections, and settings without leaving the app. See what's happening before you have to Google it.

Open DuckDB in 1bench