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.

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.

1. 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.

1. 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.

1. 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.

1. 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