Fix "null value in column violates not-null constraint" in PostgreSQL
ERROR: null value in column "signup_date" of relation "customers" violates not-null constraint
The column named in the error is marked NOT NULL, which means every row must have a value there. Your INSERT or UPDATE left it empty, either because you didn't mention the column or because the value you supplied was NULL.
Why it happens
- Leaving a required column out of the INSERT column list, when it has no default.
- INSERT ... SELECT from a source table that has NULLs in some rows.
- Setting a required column to NULL in an UPDATE, for example with NULLIF.
How to fix it
1. Include every required column
Fails
INSERT INTO customers (id, name, email, country, active_status) VALUES (11, 'Sam Okafor', 'sam@ledger.co', 'USA', 'active');
Works
INSERT INTO customers (id, name, email, country, active_status, signup_date) VALUES (11, 'Sam Okafor', 'sam@ledger.co', 'USA', 'active', CURRENT_DATE); SELECT * FROM customers ORDER BY id DESC LIMIT 3;
signup_date is NOT NULL and has no default, so it must be given. If the table is yours, ALTER TABLE customers ALTER COLUMN signup_date SET DEFAULT CURRENT_DATE fills it in automatically.
2. Handle NULLs in the source data
Fails
INSERT INTO customers (id, name, email, country, active_status, signup_date) SELECT id + 100, full_name, email, country, 'active', CURRENT_DATE FROM crm_contacts;
Works
INSERT INTO customers (id, name, email, country, active_status, signup_date) SELECT id + 100, full_name, LOWER(TRIM(email)), COALESCE(TRIM(country), 'Unknown'), 'active', CURRENT_DATE FROM crm_contacts WHERE email IS NOT NULL; SELECT id, name, email, country FROM customers WHERE id > 100 ORDER BY id;
Some contacts have no email or country. Rows without an email are skipped with WHERE, and a missing country gets 'Unknown' from COALESCE. Run SELECT COUNT(*) - COUNT(email) FROM crm_contacts first to see how many NULLs to expect.
Try it yourself
This is the broken query, running on real PostgreSQL in your browser. Press Run to see the error explained, then fix it.
Starting Postgres in your browser…
Real Postgres in your browser. Edit the query and run it, or open it in the full playground.
Practice so it doesn't happen in an interview
- Rows that would block a constraintUNIQUE · CHECK · NULL semantics · UNION
- Missing values per sourceCOUNT(*) - COUNT(col) · data quality
- Upsert a supplier price feedINSERT … ON CONFLICT DO UPDATE · EXCLUDED