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

Source: https://www.bytebase.com/reference/postgres/error/23502-null-value-violates-not-null-constraint/

---

## Error Message

```sql
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`:

```
ERROR: null value in column "email" violates not-null constraint
```

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:

```sql
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:

```sql
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`:

```sql
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

```sql
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:

```sql
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`:

```sql
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:

```sql
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.

```sql
-- 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`:

```sql
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:

```
ERROR: null value in column "email" violates not-null constraint
CONTEXT: COPY users, line 5012: "..."
```

Options, in order of preference:

```sql
-- 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:

```sql
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.

> **Note:** [Bytebase's SQL Review](https://docs.bytebase.com/sql-review/review-rules/) flags `ADD COLUMN ... NOT NULL` without a `DEFAULT` and `SET NOT NULL` on columns that still contain NULLs before the migration runs, catching the two most common 23502 sources during change review. See also [ERROR 23505: Duplicate Key Value Violates Unique Constraint](/reference/postgres/error/23505-duplicate-key-value) and [ERROR 23503: Foreign Key Violation](/reference/postgres/error/23503-foreign-key-violation) — both are integrity-constraint siblings of 23502.