PostgreSQL 23502 Error: Causes and Solutions Complete Guide

#postgres #database #dba #sql

PostgreSQL Error 23502: NOT NULL Violation

PostgreSQL error code 23502 (not_null_violation) occurs when you attempt to insert or update a row with a NULL value in a column defined with a NOT NULL constraint. This is PostgreSQL's way of enforcing data integrity — ensuring that critical fields always contain meaningful data. The error message typically looks like: ERROR: null value in column "column_name" of relation "table_name" violates not-null constraint.

Top 3 Causes

1. Missing Required Column Values in INSERT/UPDATE

The most common cause is simply forgetting to provide a value for a NOT NULL column in your query.

-- This fails if "email" is NOT NULL
INSERT INTO users (username, created_at)
VALUES ('jane_doe', NOW());
-- ERROR: null value in column "email" violates not-null constraint

-- Fix: provide the missing value
INSERT INTO users (username, email, created_at)
VALUES ('jane_doe', 'jane@example.com', NOW());

2. Adding a NOT NULL Column to an Existing Table Without a Default

When you add a new NOT NULL column to a table that already has rows, PostgreSQL can't assign NULL to existing records — causing an immediate error.

-- This fails if the table already has data
ALTER TABLE orders ADD COLUMN shipped_at TIMESTAMP NOT NULL;
-- ERROR: column "shipped_at" contains null values

-- Safe approach: add nullable first, backfill, then add constraint
ALTER TABLE orders ADD COLUMN shipped_at TIMESTAMP;

UPDATE orders
SET shipped_at = created_at + INTERVAL '2 days'
WHERE shipped_at IS NULL;

ALTER TABLE orders ALTER COLUMN shipped_at SET NOT NULL;

-- On PostgreSQL 11+, you can do this in one step with a DEFAULT
ALTER TABLE orders
    ADD COLUMN status VARCHAR(50) NOT NULL DEFAULT 'pending';

3. NULL Values Slipping Through During Data Migration or ETL

When copying data between tables using INSERT INTO ... SELECT ... or COPY, source columns may contain NULL values that conflict with NOT NULL constraints on the destination table.

-- This fails if source "phone" column has NULLs
INSERT INTO target_users (user_id, email, phone)
SELECT user_id, email, phone
FROM source_users;

-- Fix: use COALESCE to substitute NULLs with a safe default
INSERT INTO target_users (user_id, email, phone)
SELECT
    user_id,
    COALESCE(email, 'unknown@example.com'),
    COALESCE(phone, 'N/A')
FROM source_users;

-- Or filter out bad rows and log them separately
INSERT INTO migration_errors (user_id, reason)
SELECT user_id, 'NULL phone'
FROM source_users
WHERE phone IS NULL;

INSERT INTO target_users (user_id, email, phone)
SELECT user_id, email, phone
FROM source_users
WHERE phone IS NOT NULL;

Quick Fix Solutions

-- Option 1: Set a DEFAULT value so missing data is handled automatically
ALTER TABLE users
    ALTER COLUMN email SET DEFAULT 'noreply@example.com';

-- Option 2: Drop the NOT NULL constraint if it's no longer needed
ALTER TABLE users
    ALTER COLUMN phone DROP NOT NULL;

-- Option 3: Check which columns are NOT NULL in your table
SELECT column_name, is_nullable, column_default
FROM information_schema.columns
WHERE table_name = 'users'
  AND table_schema = 'public'
ORDER BY ordinal_position;

Prevention Tips

Always pair NOT NULL with a DEFAULT when altering live tables.
This avoids errors on existing rows and keeps your deployments safe. On PostgreSQL 11+, adding a column with NOT NULL DEFAULT no longer rewrites the entire table, so performance impact is minimal.

-- Safe, production-friendly pattern
ALTER TABLE users
    ADD COLUMN is_active BOOLEAN NOT NULL DEFAULT TRUE;

Validate data at the application layer before it reaches the database.
Use model-level validations (e.g., in your ORM or API layer) to catch missing required fields early. This gives users a friendly error message instead of a raw database exception, and reduces unnecessary round-trips to the database. Integrating schema-aware linting into your CI/CD pipeline can also catch column mapping mismatches before they hit production.

Related Errors

Code	Name	Description
23000	integrity_constraint_violation	Parent class for all constraint errors
23505	unique_violation	Duplicate value in a UNIQUE column
23503	foreign_key_violation	Referenced row does not exist
23514	check_violation	Value fails a CHECK constraint

📖 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.

DEV Community