ERROR 23502: Null Value in Column Violates Not-Null Constraint in Postgres

Error Message#

ERROR: null value in column "email" of relation "users" violates not-null constraint
DETAIL: Failing row contains (42, Alice, null, 2026-06-01).
SQLSTATE: 23502

The message names the column that received a NULL and the relation it belongs to. The DETAIL line prints the entire failing row, with null in the offending position — useful for spotting which value your application dropped. On older clients you may see the short form without DETAIL:

The SQLSTATE is always 23502 (not_null_violation), whether the NULL arrived from an INSERT, an UPDATE, a COPY, or a schema change that tried to enforce NOT NULL on data that already contained NULLs.

What Triggers This Error#

23502 fires whenever a write would leave a NOT NULL column holding NULL. The common triggers:

• ALTER TABLE ... ADD COLUMN ... NOT NULL without a DEFAULT, on a table that already has rows — every existing row would get NULL in the new column, so the statement is rejected outright
• ALTER TABLE ... ALTER COLUMN ... SET NOT NULL while existing rows contain NULL — the constraint can't be validated against current data
• An ORM partial insert that drops a required field — mass-assignment filtering, a missing permit/fillable entry, or a serializer that omits the column sends no value, and the column has no DEFAULT
• The application explicitly sends NULL (or None/nil) for a required field — a column DEFAULT does not apply when you pass an explicit NULL; defaults only fill omitted columns
• COPY / bulk import with missing or empty values — a short row, a CSV with a blank field mapped to a NOT NULL column, or a column-order mismatch
• A trigger or rule sets the column back to NULL — a BEFORE INSERT trigger that returns a row with the column cleared
• Inserting into a view or partitioned table where the routing target has a stricter NOT NULL than the source expected

Fix by Scenario#

Adding a NOT NULL column to a table that already has rows#

ADD COLUMN ... NOT NULL with no default fails immediately because every existing row would violate the constraint:

ALTER TABLE users ADD COLUMN status text NOT NULL;
-- ERROR: column "status" of relation "users" contains null values

Give the column a DEFAULT in the same statement. Since PostgreSQL 11 this is a metadata-only operation — it does not rewrite the table or take a long lock, even on large tables:

ALTER TABLE users ADD COLUMN status text NOT NULL DEFAULT 'active';

If the correct value differs per row and there's no single sensible default, do it in three safe steps: add the column nullable, backfill in batches, then enforce NOT NULL:

ALTER TABLE users ADD COLUMN status text;          -- nullable, instant
UPDATE users SET status = 'active' WHERE status IS NULL;  -- backfill (batch for large tables)
ALTER TABLE users ALTER COLUMN status SET NOT NULL;       -- enforce

Setting NOT NULL on a column that still has NULLs#

ALTER TABLE orders ALTER COLUMN customer_id SET NOT NULL;
-- ERROR: column "customer_id" of relation "orders" contains null values

Find and resolve the NULL rows first:

SELECT count(*) FROM orders WHERE customer_id IS NULL;
-- inspect them, then either backfill or delete
UPDATE orders SET customer_id = 0 WHERE customer_id IS NULL;  -- or a real value

On a large production table, avoid the full-table ACCESS EXCLUSIVE scan that SET NOT NULL performs by adding a validated CHECK constraint first — Postgres 12+ will reuse it to skip the scan when you set NOT NULL:

ALTER TABLE orders ADD CONSTRAINT orders_customer_id_not_null
  CHECK (customer_id IS NOT NULL) NOT VALID;          -- no scan, brief lock
ALTER TABLE orders VALIDATE CONSTRAINT orders_customer_id_not_null;  -- scan without blocking writes
ALTER TABLE orders ALTER COLUMN customer_id SET NOT NULL;            -- reuses the validated check, no second scan
ALTER TABLE orders DROP CONSTRAINT orders_customer_id_not_null;      -- optional cleanup

ORM partial insert drops a required field#

When an ORM sends an INSERT without a required column, the column gets NULL (no value was supplied and there's no default). The fix is almost never a database change — it's making sure the value reaches the model:

• Rails — a column missing from strong parameters (permit) is silently filtered out before it ever reaches the insert. Confirm the attribute is permitted and present.
• Django — a model field with null=False (the default) and no value set raises IntegrityError. Set the field, or give it a default=.
• Sequelize / Prisma — a field absent from the create payload is omitted from the INSERT. Add it, or declare a default in the schema.

If the column genuinely has a sensible fallback, put the default in the database so every path is covered, not just the ORM:

ALTER TABLE users ALTER COLUMN status SET DEFAULT 'active';

A database DEFAULT only applies when the column is omitted from the INSERT — so make the ORM omit it rather than sending an explicit NULL (see next scenario).

Explicit NULL sent instead of omitting the column#

This is the subtlest 23502: the column has a DEFAULT, but you still get the error because the statement passed NULL explicitly.

-- Column defined as: status text NOT NULL DEFAULT 'active'
INSERT INTO users (name, status) VALUES ('Alice', NULL);
-- ERROR: null value in column "status" ... — the DEFAULT did NOT apply

A DEFAULT fills a column only when that column is left out of the INSERT. To force the default explicitly, use the DEFAULT keyword instead of NULL:

INSERT INTO users (name, status) VALUES ('Alice', DEFAULT);  -- uses 'active'
-- or simply omit the column:
INSERT INTO users (name) VALUES ('Alice');                   -- uses 'active'

In application code this usually means: don't map an empty form field to NULL for a column that should fall back to its default — omit the key from the insert payload.

COPY or bulk import with missing values#

A COPY row that is short, or a CSV blank mapped to a NOT NULL column, raises 23502 mid-load:

Options, in order of preference:

-- 1. Tell COPY which string means "use the default" is not possible; instead
--    map blanks to a real value during load via a staging table:
CREATE TEMP TABLE users_stage (LIKE users INCLUDING DEFAULTS);
ALTER TABLE users_stage ALTER COLUMN email DROP NOT NULL;  -- relax on staging only
COPY users_stage FROM '/path/users.csv' WITH (FORMAT csv, HEADER);
INSERT INTO users
SELECT id, name, COALESCE(NULLIF(email, ''), 'unknown@example.com'), created_at
FROM users_stage;

Load into a permissive staging table, clean the NULLs/blanks with COALESCE, then insert into the real table. Never relax NOT NULL on the production table just to get an import through.

A trigger clears the column#

If a BEFORE INSERT/BEFORE UPDATE trigger returns NEW with the column set to NULL, 23502 fires after the trigger runs. Inspect triggers on the table:

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

Fix the trigger body so it preserves (or populates) the column instead of nulling it.

Prevention#

• When adding a NOT NULL column to a populated table, always include a DEFAULT in the same statement (metadata-only since PG 11), or use the add-nullable → backfill → SET NOT NULL sequence.
• For large tables, enforce NOT NULL via a CHECK (... IS NOT NULL) NOT VALID → VALIDATE → SET NOT NULL sequence to avoid a blocking full-table scan.
• Remember that a column DEFAULT applies only to omitted columns — passing an explicit NULL bypasses it. In application code, omit the key rather than sending NULL.
• Make required-field validation live in the application layer too, so the database NOT NULL is the last line of defense, not the first error users see.
• Bulk-load through a permissive staging table and COALESCE blanks to real values; never drop NOT NULL on the production table to force an import.
• Audit BEFORE triggers when a column mysteriously arrives NULL despite the application sending a value.