Incident brief
Null value in column violates not-null constraint
A column was declared NOT NULL, and an INSERT or UPDATE tried to leave it null anyway. PostgreSQL rejects the row before it is ever stored.
In 10 seconds
- What
- Null value in column violates not-null constraint
- What triggers it
- Create a table with a column marked NOT NULL.
- The fix
- Provide a real value for the column on every INSERT/UPDATE.
- Proof
- Reproduced on PostgreSQL 16.14 → The INSERT that left email NULL was rejected with SQLSTATE 23502 and the row was never stored — the table stayed empty.
The fix
What to do right now
The immediate, application-level response to this error.
- Provide a real value for the column on every INSERT/UPDATE.
- If a value is genuinely optional, remove the NOT NULL constraint instead of forcing a placeholder.
- Do not rely on a column DEFAULT to fill in NULL — a DEFAULT of NULL is still NULL, and NOT NULL still rejects it.
CREATE TABLE billing.customers (
id integer primary key,
email text NOT NULL
);
-- provide a real value instead of NULL
INSERT INTO billing.customers (id, email) VALUES (1, 'a@example.com');Diagnose
See it live on the server
Run these against the affected instance to confirm the diagnosis before you act.
Standard triage — not specific to this error
These are canonical PostgreSQL system-catalog queries, shown as SQL to run. No sample output is attached because this is general triage, not a captured lab transcript.This SQLSTATE does not have an error-specific live snapshot yet. These are the canonical system-catalog queries you run against the affected server to see the problem in real time — standard triage, not a reproduced transcript.
What is running right now
Active backends, how long each has been running, and what it is waiting on.
SELECT pid,
state,
wait_event_type,
wait_event,
now() - query_start AS running_for,
left(query, 80) AS query
FROM pg_stat_activity
WHERE state <> 'idle'
AND pid <> pg_backend_pid()
ORDER BY running_for DESC NULLS LAST;Who is blocking whom
Turn raw blocking PIDs into the actual queries on both sides of the wait.
SELECT blocked.pid AS blocked_pid,
blocked.query AS blocked_query,
blocking.pid AS blocking_pid,
blocking.query AS blocking_query
FROM pg_stat_activity AS blocked
JOIN LATERAL unnest(pg_blocking_pids(blocked.pid)) AS b(pid) ON true
JOIN pg_stat_activity AS blocking ON blocking.pid = b.pid
WHERE cardinality(pg_blocking_pids(blocked.pid)) > 0;Locks that are still waiting
Every lock a backend has requested but not yet been granted.
SELECT l.pid,
l.locktype,
l.mode,
l.granted,
COALESCE(c.relname, l.transactionid::text) AS object
FROM pg_locks l
LEFT JOIN pg_class c ON c.oid = l.relation
WHERE NOT l.granted
ORDER BY l.pid;Why it happens
What PostgreSQL is telling you
The mechanism behind the error, grounded in the official manual — not paraphrased.
PostgreSQL 16 Documentation — §5.4.2 Not-Null Constraints
A not-null constraint simply specifies that a column must not assume the null value.Read the full section on postgresql.org →
The insert that violates NOT NULL
PostgreSQL checks the NOT NULL constraint before the row is stored. The DETAIL line shows exactly which row was rejected and why — email was null, and that's not allowed.Checking the table afterward
The table is empty. The rejected INSERT never happened — there's no half-written row to clean up.Reproduce & verify
A real, single-session PostgreSQL reproduction
A literal transcript of SQL run against a live PostgreSQL instance in an isolated lab — the commands below are exactly what was executed.
- 1Create a table with a column marked NOT NULL.
- 2Try to insert a row that leaves that column NULL.
- 3PostgreSQL rejects the row with SQLSTATE 23502 before it is stored.
One client: create a NOT NULL column, try to insert a NULL into it, then check the table.
DROP TABLE IF EXISTS billing.customers;
CREATE TABLE billing.customers (
id integer primary key,
email text NOT NULL
);INSERT INTO billing.customers (id, email) VALUES (1, NULL);SELECT * FROM billing.customers;What PostgreSQL actually returned
DROP TABLE
CREATE TABLEERROR: null value in column "email" of relation "customers" violates not-null constraint
DETAIL: Failing row contains (1, null). id | email
----+-------
(0 rows)A column declared NOT NULL DEFAULT NULL might look like it 'defaults to allowed' — but the manual says the constraint applies even to values that come from a DEFAULT. This was tested directly.
Create a column that is NOT NULL with DEFAULT NULL, then insert a row that omits the column entirely, letting its own DEFAULT supply the value.
Without this
Above: an explicit NULL is rejected by NOT NULL.
With this, tested
Below: omitting the column and relying on its own DEFAULT NULL is rejected the same way.
What Pro unlocks here
- The exact prevention SQL — copy-paste ready
- Raw psql output captured from the Docker lab
- A senior-DBA action list to take it further
- Live monitoring queries to catch it in production
- The deeper audit: fix-that-fails counterexample, GUC before/after, server-log evidence
Related & next steps
Follow the thread
Everything this error touches — jump straight to the sibling error, term, runbook, or parameter.
Verification
- Last verified
- 2026-07-15 (Docker lab, PostgreSQL 16.14)
- Reviewed by
- Verified against PostgreSQL 16.14 in an isolated lab environment
- Audit status
- reviewed