🚨 PostgreSQL Error Guide: 'null value in column violates not-null constraint' — Fix

Overview

PostgreSQL raises this whenever a row would leave a NOT NULL column empty — on an INSERT that omits the column, an UPDATE that sets it to NULL, or an ALTER TABLE ... SET NOT NULL run against a table that still holds legacy NULLs:

ERROR:  null value in column "email" of relation "users" violates not-null constraint
DETAIL:  Failing row contains (1042, Ada, null, 2026-07-09 10:15:00).

The DETAIL line is the most useful part: it prints the entire failing row, so you can see exactly which value arrived as null.

Symptoms

• An INSERT or UPDATE from application code fails and the transaction rolls back.
• A data migration or ALTER TABLE ... SET NOT NULL aborts partway with this error.
• A bulk COPY load stops on the first offending row.
• An ORM save fails intermittently — only for records where an optional field was left blank.
• The same statement works in one environment and fails in another because a column default exists in one and not the other.

Common Root Causes

• The INSERT omits a required column that has no default. The column resolves to NULL and violates the constraint.
• Application sent an explicit NULL — an unset object field, an empty form value, or a JSON null mapped straight through.
• A DEFAULT was expected but doesn’t exist (or was dropped), so the omitted column is not backfilled.
• A BEFORE INSERT/UPDATE trigger returned NULL for the column, or a trigger that was supposed to populate it is disabled or errored silently.
• ALTER TABLE … SET NOT NULL on a table with existing NULL rows — the constraint is validated against all current data and fails on legacy rows.
• A failed sequence/default expression — e.g. a generated column or default function returned NULL.
• INSERT … SELECT where the source query produces NULL for that column (an outer join with no match, a NULLIF, etc.).

Diagnostic Workflow

Read the DETAIL row first — it names the column and shows every value, so you can see which one is null.

Confirm the column really is NOT NULL and whether it has a default:

SELECT column_name, is_nullable, column_default
FROM information_schema.columns
WHERE table_name = 'users'
ORDER BY ordinal_position;

If this is an ALTER TABLE ... SET NOT NULL failure, find the existing NULL rows before retrying:

SELECT count(*) FROM users WHERE email IS NULL;
SELECT id FROM users WHERE email IS NULL LIMIT 20;

If a trigger is meant to populate the column, list triggers and check they’re enabled:

SELECT tgname, tgenabled, pg_get_triggerdef(oid)
FROM pg_trigger
WHERE tgrelid = 'users'::regclass AND NOT tgisinternal;

To see whether the app is sending an explicit NULL versus omitting the column, log the offending statement:

SET log_min_error_statement = 'error';   -- logs the failing SQL with its parameters

Check the full table definition, including defaults and generated columns:

\d+ users

Example Root Cause Analysis

A service began failing on user signup with null value in column "tenant_id" of relation "users" violates not-null constraint. The DETAIL row showed tenant_id as null.

Inspecting the schema, tenant_id was NOT NULL with no default. The application previously relied on a BEFORE INSERT trigger to copy tenant_id from a session setting:

SELECT tgname, tgenabled FROM pg_trigger
WHERE tgrelid = 'users'::regclass AND NOT tgisinternal;

 tgname               | tgenabled
----------------------+-----------
 set_tenant_on_insert | D

tgenabled = 'D' — the trigger was disabled, left that way after a migration script ran ALTER TABLE users DISABLE TRIGGER ALL and never re-enabled it. With the trigger off, tenant_id was never populated, so every insert supplied NULL. Re-enabling the trigger restored inserts:

ALTER TABLE users ENABLE TRIGGER set_tenant_on_insert;

The real fix was making the application pass tenant_id explicitly rather than depending on a trigger, so a future disable couldn’t silently break inserts.

Prevention Best Practices

• Give required columns a sensible DEFAULT where one exists (now(), a sequence, a literal) so an omitted value doesn’t become NULL.
• Have the application send required fields explicitly instead of relying on triggers or defaults that can be disabled or dropped.
• Before ALTER TABLE ... SET NOT NULL, backfill existing NULLs and validate: UPDATE ... SET col = <value> WHERE col IS NULL; then add the constraint (on large tables, add a CHECK (col IS NOT NULL) NOT VALID then VALIDATE CONSTRAINT to avoid a long lock).
• Re-enable any triggers a migration disables, and prefer targeted DISABLE TRIGGER <name> over DISABLE TRIGGER ALL.
• Validate inputs at the application boundary so empty form fields don’t map to NULL for required columns.
• In INSERT ... SELECT, use COALESCE(source_col, <fallback>) where an outer join can produce NULL.

Quick Command Reference

-- Which columns are NOT NULL and what are their defaults?
SELECT column_name, is_nullable, column_default
FROM information_schema.columns WHERE table_name = 'users';

-- Find existing NULL rows before SET NOT NULL
SELECT count(*) FROM users WHERE email IS NULL;

-- Full table definition
\d+ users

-- List triggers and whether they're enabled
SELECT tgname, tgenabled FROM pg_trigger
WHERE tgrelid = 'users'::regclass AND NOT tgisinternal;

-- Safe path to add NOT NULL on a large table
ALTER TABLE users ADD CONSTRAINT users_email_nn CHECK (email IS NOT NULL) NOT VALID;
ALTER TABLE users VALIDATE CONSTRAINT users_email_nn;

Conclusion

The DETAIL: Failing row contains (...) line makes this one of the easier Postgres errors to diagnose — it hands you the exact column and value. The fix splits cleanly by cause: for an INSERT/UPDATE, either supply the value or add a default; for a disabled or broken trigger, restore it and then remove the app’s dependence on it; and for ALTER TABLE ... SET NOT NULL, backfill the legacy NULLs first and use a NOT VALID check to avoid a long lock. Push required-field validation to the application boundary and the error stops recurring.