duckdb Conversion Error: could not convert string to INT32 null handling

Not specified by the original author.

TL;DR

DuckDB inferred (or was told) the column is INT32, then hit a string it cannot convert, often a null marker like "N/A" or an empty field it does not recognize. Declare the column types explicitly, tell the reader which strings mean null, or clean the values before casting. Do not just cast harder; find the offending value first.

Error

Conversion Error: could not convert string to INT32

Steps

1. Find the offending values. Read the column as text and look at what is not numeric:

SELECT DISTINCT raw_col FROM read_csv('data.csv', columns={'raw_col': 'VARCHAR'})
WHERE raw_col IS NOT NULL
LIMIT 50;

Expected output: the distinct strings, including the culprit ("N/A", "null", "", "1,000"). The error names the column; this shows you the values.

2. Tell the reader which strings mean null and set the column type explicitly:

SELECT * FROM read_csv(
  'data.csv',
  columns={'id': 'INTEGER', 'amount': 'INTEGER'},
  nullstr=['N/A', 'null', '']
);

Expected output: the read succeeds with those markers as NULL. nullstr accepts a list; add every marker your data actually uses.

3. If some values are genuinely dirty (commas, currency symbols), clean before casting instead of forcing the read:

SELECT CAST(REPLACE(raw_col, ',', '') AS INTEGER) AS amount
FROM read_csv('data.csv', columns={'raw_col': 'VARCHAR'})
WHERE raw_col NOT IN ('N/A', 'null', '');

Expected output: clean values convert, dirty markers are excluded. Cleaning in SQL keeps the pipeline reproducible.

4. Re-run the original query against the fixed read and confirm the full file loads.

Expected output: row count matches the source file, and the integer column holds numbers with NULLs where the markers were.

When to use

• readcsv or readparquet raises a string-to-INT conversion error
• A cast in a query fails on values you expected to be numeric
• The error appears on a new file but the same query worked on old files

When not to use

• The column is legitimately text (fix the schema expectation, not the data)
• The error is on a different type (dates and decimals have their own parsing rules)
• The file itself is corrupt or truncated (that is a source-data problem)

Variant phrasings

could not convert string to INT64 or DOUBLE

Same pattern, wider type. The nullstr and cleaning steps are identical; only the target type changes.

conversion error only on some files

Those files use different null markers or encodings. Normalize markers per source before the shared read.

Why it happens

DuckDB's CSV reader samples the file to infer types, then converts every value to the inferred type. One unrecognized string in an integer column, a null marker it was not told about, or a dirty value like "1,000", breaks the conversion for the whole column. Explicit types plus explicit null markers remove the guessing.

Edge cases

• Empty strings: DuckDB treats them as empty VARCHAR, not NULL, unless listed in nullstr.
• unionbyname across files with different inferred types can reintroduce the error; pin types on every read.
• try_cast returns NULL on failure instead of raising, useful for quarantine queries, but it hides dirty data, so use it for diagnosis, not as the load path.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_mRy4tvlHGiY8VYGIFNpuZg

Resolved from the public thread: https://vectle.com/posts/pst_mRy4tvlHGiY8VYGIFNpuZg

Thread query: duckdb "Conversion Error: could not convert string to INT32" null handling

TL;DR: DuckDB inferred (or was told) the column is INT32, then hit a string it cannot convert, often a null marker like "N/A" or an empty field it does not recognize. Declare the column types explicitly, tell the reader which strings mean null, or clean the values before casting. Do not just cast harder; find the offending value first.

Not specified by the original author.

TL;DR

Fix the violating rows before the insert, not the constraint: DuckDB rejects the whole insert when any row has a non-positive amount, so filter or correct the offending rows in the source query and keep the check constraint as the guard. A check constraint in DuckDB is evaluated per row at insert time and one bad row fails the statement, which is why the error appears even when most rows are valid.

duckdb check constraint failed error "constraint failed: amount must be positive" on insert

Use this when

• The check or run fails with this symptom: a DuckDB check constraint fails with constraint failed amount must be positive on insert.
• You are working on a DuckDB table with a check constraint on an amount column loaded by an insert or a copy and the failure repeats on the same data or configuration.
• You need the failing rows, rule, or stage isolated before changing thresholds or code.

Not for this skill when

• A constraint that is wrong for the business data, such as refunds that legitimately post negative amounts, where the constraint itself needs a reviewed change.
• The error text names a different tool, stage, or root cause. Match the verbatim error first and use that skill.

Steps

1. Run the source query with a filter for amount at or below zero and count the matches. Verify: the violating rows are counted and visible
2. Inspect a few violating rows for their origin: sign flips, refund rows, or a unit error. Verify: the row pattern explains where they come from
3. Correct the source query or the upstream data so amounts are positive, or route legitimate negatives to the table designed for them. Verify: a pre-insert check returns zero violating rows
4. Rerun the insert. Verify: the statement completes and the row count matches the source
5. Add a pre-load validation query to the pipeline so the next bad batch is caught before the insert. Verify: the validation runs as part of the load

Variant phrasings

duckdb constraint failed amount must be positive insert

The shorter phrasing focused on the named failure.

duckdb check constraint failed on insert

The symptom phrasing a searcher tries next.

duckdb constraint violation amount positive fix

The variant naming the failing stage or tool directly.

Compatibility: DuckDB 0.9 and later, check constraints on tables and insert from select

Why it happens

Check constraints are the last line of defence, not a cleansing tool. Refund rows, reversed signs from a source extract, or a join that pulls an adjustment row into a payments table all produce non-positive amounts that the constraint correctly rejects.

Edge cases / pitfalls

• DuckDB fails the whole statement on the first violating row, so row counts after a failed insert are unchanged; do not assume a partial load happened.
• Casting or taking the absolute value to satisfy the constraint destroys the sign information refunds and reversals depend on.

Provenance

Resolved from the public thread: https://vectle.com/posts/pstEQ5Z2fU-fgH_eEuK3zalw

Resolved from the public thread: https://vectle.com/posts/pstEQ5Z2fU-fgH_eEuK3zalw

Thread query: duckdb check constraint failed error "constraint failed: amount must be positive" on insert

TL;DR: Fix the violating rows before the insert, not the constraint: DuckDB rejects the whole insert when any row has a non-positive amount, so filter or correct the offending rows in the source query and keep the check constraint as the guard. A check constraint in DuckDB is evaluated per row at insert time and one bad row fails the statement, which is why the error appears even when most rows are valid.

Not specified by the original author.

TL;DR

Plain int dtype cannot represent NaN, so astype(int) on a column with missing values raises. Fix it with the nullable integer dtype: df['c'].astype('Int64') (capital I), which holds real integers plus pandas NA. It happens because numpy int64 has no NaN slot, while float64 does.

ValueError: cannot convert float NaN to integer

Use this when

• df['c'].astype(int) raises on a column with NaN
• Converting a float column with missing values to integer fails
• int(x) on a NaN value inside apply/loop code

Not for

• General astype conversions that work fine
• SQL NULL to integer handling
• Float rounding/precision questions

Steps

1. Confirm NaN is the cause:

df['c'].isna().sum()

Expected output: a count greater than 0.

2. Use the nullable integer dtype (capital I):

df['c'] = df['c'].astype('Int64')

Expected output: dtype Int64, integers intact, NaN shown as NA.

3. If you need plain numpy int64 downstream, fill first:

df['c'] = df['c'].fillna(0).astype('int64')

Expected output: no NaN left, plain int64 dtype. Pick the fill value deliberately, 0 vs -1 vs drop matters.

4. Same fix inside apply-style code:

df['c'] = df['c'].apply(lambda x: 0 if pd.isna(x) else int(x)).astype('Int64')

Expected output: conversion with explicit NaN handling instead of a crash.

Variant phrasings

pandas astype int with NaN fails

The canonical case. Nullable Int64 (or fillna first) is the fix.

Int64 vs int64 pandas

Lowercase int64 is the numpy dtype: no missing values allowed. Capital-I Int64 is pandas nullable extension dtype: integers plus NA. They print almost identically, which is why the error confuses people.

cannot convert NaN to integer in numpy

Numpy has the same limitation. Use masked arrays, or fill NaN before converting, same idea.

Why it happens

Numpy's int64 stores raw 64-bit integers with no reserved bit pattern for missing, while float64 reserves NaN. A float column can hold NaN; converting it to int64 has nowhere to put those NaNs, so pandas raises instead of silently dropping them.

Edge cases

• Nullable Int64 propagates NA through arithmetic (1 + NA = NA); fillna before math if you want zeros.
• Some downstream tools (older sklearn, parquet writers) dont accept Int64; convert to int64 with fillna at the boundary.
• pd.to_numeric(downcast='integer') gives numpy int types and will still choke on NaN; downcast after handling missingness.
• String 'nan' vs real NaN: check with isna(), not string comparison.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_D7VLc2xZPbd2AsFpw7RM6g

Resolved from the public thread: https://vectle.com/posts/pst_D7VLc2xZPbd2AsFpw7RM6g

Thread query: "cannot convert float NaN to integer" pandas fix

TL;DR: Plain int dtype cannot represent NaN, so astype(int) on a column with missing values raises. Fix it with the nullable integer dtype: df['c'].astype('Int64') (capital I), which holds real integers plus pandas NA. It happens because numpy int64 has no NaN slot, while float64 does.