PostgreSQL 23502 Error: Causes and Solutions Complete Guide

PostgreSQL Error 23502: NOT NULL Violation — Causes, Fixes & Prevention

PostgreSQL error code 23502 (not_null_violation) is thrown when you attempt to insert or update a row with a NULL value in a column defined as NOT NULL. This constraint exists to enforce data integrity by guaranteeing that critical columns always hold a meaningful value. It triggers an immediate transaction rollback, so understanding how to resolve it quickly is essential for any developer or DBA.

---

Top 3 Causes

1. Missing Value in INSERT or UPDATE

The most common cause: you simply forget to supply a value for a required column, or you explicitly pass NULL.

-- This will fail
INSERT INTO orders (customer_id, product_id, quantity)
VALUES (1, 42, NULL);
-- ERROR:  null value in column "quantity" of relation "orders"
--         violates not-null constraint

-- Fix: provide a valid value
INSERT INTO orders (customer_id, product_id, quantity)
VALUES (1, 42, 1);

-- Or set a default so omitting the column is safe
ALTER TABLE orders ALTER COLUMN quantity SET DEFAULT 1;

2. Adding a NOT NULL Column to a Table with Existing Data

If you add a NOT NULL column without a DEFAULT, PostgreSQL cannot fill in existing rows and raises this error immediately.

-- Fails when the table already has rows
ALTER TABLE users ADD COLUMN last_login TIMESTAMP NOT NULL;
-- ERROR:  column "last_login" of relation "users" contains null values

-- Safe 3-step approach
-- Step 1: add as nullable
ALTER TABLE users ADD COLUMN last_login TIMESTAMP;

-- Step 2: back-fill existing rows
UPDATE users SET last_login = NOW() WHERE last_login IS NULL;

-- Step 3: enforce the constraint
ALTER TABLE users ALTER COLUMN last_login SET NOT NULL;

3. NULL Values Coming from External Data Sources

Bulk loads via COPY, ETL pipelines, or INSERT INTO ... SELECT can bring in unexpected NULL values from source data that doesn't match your schema's expectations.

-- Risky bulk insert from a staging table
INSERT INTO customers (id, email, status)
SELECT id, email, status FROM staging_customers;
-- Fails if any email or status is NULL in staging

-- Safe version using COALESCE to handle NULLs
INSERT INTO customers (id, email, status)
SELECT
    id,
    COALESCE(email, '[e-mail]'),
    COALESCE(status, 'ACTIVE')
FROM staging_customers
WHERE id IS NOT NULL;

-- Pre-load validation query
SELECT COUNT(*) AS problematic_rows
FROM staging_customers
WHERE email IS NULL OR status IS NULL;

---

Quick Fix Summary

Scenario	Fix
Missing column value	Provide an explicit value or set a DEFAULT
Adding column to existing table	Use the 3-step nullable → back-fill → constrain approach
Bulk load with dirty data	Use COALESCE / filter NULL rows before inserting

---

Prevention Tips

Validate early, before hitting the database.
Enforce NOT NULL rules at the application layer (e.g., ORM model validations, JSON Schema, Pydantic models) so errors surface before a query is even sent to PostgreSQL. This reduces round-trips and gives users clearer error messages.

Test schema migrations against production-like data.
Always run ALTER TABLE scripts on a staging environment loaded with a realistic dataset before applying them to production. Include a pre-migration check query that counts NULL values in affected columns, and always prepare a rollback script.

-- Pre-migration check: ensure no NULLs exist before adding NOT NULL
SELECT COUNT(*) AS null_count
FROM users
WHERE last_login IS NULL;
-- Must return 0 before proceeding with ALTER TABLE

---

Related Error Codes

• 23000 — integrity_constraint_violation: Parent class of 23502; catches all constraint violations.
• 23505 — unique_violation: Duplicate value in a unique or primary key column.
• 23503 — foreign_key_violation: Referenced row does not exist in the parent table.

---

📖 Want a more detailed guide?
Check out the full in-depth version (Korean) on oraerror.com — includes detailed analysis, additional SQL examples, and prevention tips.