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

Find the NULL-producing rows in your SELECT before the INSERT runs, then decide: COALESCE them to a default, filter them out, or fix the source. The error means a NOT NULL constraint rejected a NULL value, so the fix is either stop producing NULLs or stop forbidding them, and you want evidence for which is right.

ERROR: null value in column "email" of relation "users" violates not-null constraint

Use this when

• INSERT, COPY, or an ETL load fails on a NOT NULL column
• You need to find which rows carry the NULLs
• You are unsure whether to clean data or change the constraint

Not for this skill when

• NULLs show up in SELECT results but nothing fails (thats logic, not a constraint)
• The column is nullable and the bug is downstream
• You are handling NaN in pandas

Steps

1. Reproduce the NULLs with the SELECT alone, before the INSERT:

SELECT id, email
FROM staging_users
WHERE email IS NULL
LIMIT 20;

Expected output: the offending rows. If this returns nothing, the NULL comes from a join or expression in the full query, not the raw staging data, so widen the SELECT to match the INSERT exactly.

2. Check whether a join is manufacturing the NULLs:

SELECT s.id, s.email, m.normalized_email
FROM staging_users s
LEFT JOIN email_map m ON s.email = m.raw_email
WHERE m.normalized_email IS NULL
LIMIT 20;

Expected output: rows where the lookup missed. A LEFT JOIN that finds no match produces NULLs for every right-side column, which then violate NOT NULL on insert.

3. Decide per column: default it, drop the rows, or fix the source. Defaulting is the common case:

INSERT INTO users (id, email, created_at)
SELECT id,
       COALESCE(NULLIF(TRIM(email), ''), 'unknown'),
       COALESCE(created_at, now())
FROM staging_users;

Expected output: the insert succeeds. COALESCE supplies the default only where the value is actually NULL, and NULLIF catches empty strings pretending not to be NULL.

4. If the rows are junk, filter them instead of inventing data URIs

INSERT INTO users (id, email)
SELECT id, email FROM staging_users
WHERE email IS NOT NULL AND TRIM(email) <> '';
-- log what you dropped
SELECT count(*) AS dropped FROM staging_users
WHERE email IS NULL OR TRIM(email) = '';

Expected output: a clean insert plus a dropped count you can alert on. Dropping silently is how data goes missing; always count it.

5. Only relax the constraint when NULL is legitimately meaningful for the business:

ALTER TABLE users ALTER COLUMN phone DROP NOT NULL;

Expected output: the constraint is gone. Do this when "no phone number" is a real state, not when the pipeline is just sloppy. A constraint you drop to make a broken load pass will never protect you again.

Variant phrasings

null value violates not-null constraint postgres

The exact error. Steps 1-2 locate the NULLs, steps 3-5 are the three possible resolutions.

COPY null value violates not null constraint

COPY has no expressions, so you cant COALESCE inline. Load into a staging table first, then INSERT ... SELECT with the fixes from step 3.

cannot insert NULL into column SQL Server

Same diagnosis. SQL Server phrases it as "Cannot insert the value NULL into column"; the NULL-tracing steps are identical.

Why it happens

NOT NULL is a contract: this column always has a value. Loads break it when upstream data changes shape (a new source sends blanks), when a join misses (unmatched dimension keys), or when an expression yields NULL on unexpected input (division, string parsing). The constraint did its job by failing loudly; the fix belongs at whichever layer broke the contract.

Edge cases

• Empty string vs NULL: Postgres treats them as different, and a NOT NULL column happily accepts ''. Decide which one your data means.
• ORM upserts that omit a column insert the column default, or NULL if there is none; check what the ORM sends, not just the model.
• Multi-row INSERT fails atomically: one bad row rolls back the whole statement. Validate in staging (step 1) before the real insert.
• DEFAULT only applies when the column is omitted from the INSERT list, not when you explicitly insert NULL.

Provenance

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

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

Thread query: SQL "cannot insert null into column" debugging

TL;DR: Find the NULL-producing rows in your SELECT before the INSERT runs, then decide: COALESCE them to a default, filter them out, or fix the source. The error means a NOT NULL constraint rejected a NULL value, so the fix is either stop producing NULLs or stop forbidding them, and you want evidence for which is right.

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.