How to Fix PostgreSQL Error: Null Value Violates Not-Null Constraint

The "Null value violates not-null constraint" error occurs when attempting to insert or update a row with a NULL value in a column that has a NOT NULL constraint. This constraint ensures that every row must have a value for that column, preventing incomplete or missing data.

Impact

This error prevents INSERT and UPDATE operations from completing, causing application failures when required data is missing. It's common when form validation is insufficient, during data migrations, or when APIs receive incomplete data.

Common Causes

Missing required field in INSERT statement
Explicitly setting column to NULL
Application not validating required fields
Default value not specified for new columns
Data migration with incomplete source data
Column made NOT NULL without handling existing NULLs
Trigger or function setting value to NULL

Troubleshooting and Resolution Steps

Identify the column and provide a value:

-- Error: null value in column "email" of relation "users" violates not-null constraint

-- WRONG: Missing required column
INSERT INTO users (name) VALUES ('John Doe');

-- CORRECT: Include all required columns
INSERT INTO users (name, email)
VALUES ('John Doe', 'john@example.com');

Check table constraints:

-- View table structure and constraints
\d users

-- Query constraints
SELECT column_name, is_nullable, column_default
FROM information_schema.columns
WHERE table_name = 'users' AND is_nullable = 'NO';

Use default values:

-- Set default value for column
ALTER TABLE users ALTER COLUMN created_at SET DEFAULT CURRENT_TIMESTAMP;
ALTER TABLE users ALTER COLUMN status SET DEFAULT 'active';
ALTER TABLE users ALTER COLUMN count SET DEFAULT 0;

-- Now INSERT can omit these columns
INSERT INTO users (name, email) VALUES ('John', 'john@example.com');
-- created_at, status, and count will use defaults

Add column with default for existing rows:

-- Add NOT NULL column with default value
ALTER TABLE users ADD COLUMN phone VARCHAR(20) DEFAULT '';

-- Add NOT NULL column in two steps
ALTER TABLE users ADD COLUMN phone VARCHAR(20);
UPDATE users SET phone = '' WHERE phone IS NULL;
ALTER TABLE users ALTER COLUMN phone SET NOT NULL;

Make column nullable if appropriate:

-- Remove NOT NULL constraint
ALTER TABLE users ALTER COLUMN middle_name DROP NOT NULL;

-- Now NULL values are allowed
INSERT INTO users (name, email, middle_name)
VALUES ('John Doe', 'john@example.com', NULL);

Handle existing NULL values before adding NOT NULL:

-- Check for existing NULLs
SELECT COUNT(*) FROM users WHERE email IS NULL;

-- Update NULL values
UPDATE users SET email = 'unknown@example.com' WHERE email IS NULL;

-- Now add NOT NULL constraint
ALTER TABLE users ALTER COLUMN email SET NOT NULL;

Use COALESCE for conditional defaults:

-- Provide fallback value if NULL
INSERT INTO users (name, email, phone)
VALUES ('John', 'john@example.com', COALESCE(NULL, 'N/A'));

-- In UPDATE
UPDATE users
SET phone = COALESCE(new_phone, current_phone, 'N/A')
WHERE id = 123;

Handle NULL in application before INSERT:

# Python example with psycopg2
def create_user(name, email, phone=None):
    # Ensure required fields have values
    if not email:
        raise ValueError("Email is required")

    # Provide default for optional fields that are NOT NULL
    phone = phone or ''

    cursor.execute(
        "INSERT INTO users (name, email, phone) VALUES (%s, %s, %s)",
        (name, email, phone)
    )

Use generated columns for computed values:

-- Create generated column (PostgreSQL 12+)
ALTER TABLE users ADD COLUMN full_name TEXT
GENERATED ALWAYS AS (first_name || ' ' || last_name) STORED;

-- generated columns are automatically populated

Transaction-safe migration:

-- Safe migration with NOT NULL
BEGIN;

-- Add column as nullable
ALTER TABLE users ADD COLUMN new_column VARCHAR(100);

-- Populate with values
UPDATE users SET new_column = 'default_value';

-- Make it NOT NULL
ALTER TABLE users ALTER COLUMN new_column SET NOT NULL;

COMMIT;

Additional Information

NOT NULL constraints are checked immediately on INSERT/UPDATE
Default values are applied before constraint checking
Generated columns automatically populate values
Check constraints can provide more complex validation
Use application-level validation as first line of defense
Consider using empty strings vs NULL based on business logic
Document which fields are required in API/schema documentation

Frequently Asked Questions

Q: What's the difference between NULL and an empty string?
A: NULL means "no value" or "unknown", while an empty string ('') is a value that happens to be blank. NOT NULL constraints prevent NULL but allow empty strings.

Q: Can a column have both NOT NULL and a default value?
A: Yes, this is common. The default ensures INSERT without that column still works, while NOT NULL prevents explicit NULL insertions.

Q: How do I make a primary key column?
A: Primary keys automatically include NOT NULL: ALTER TABLE users ADD PRIMARY KEY (id); or during creation: id SERIAL PRIMARY KEY.

Q: Can I add NOT NULL to a column with existing NULL values?
A: No, you must first UPDATE all NULL values to non-NULL, then add the constraint.

Q: What happens if I omit a NOT NULL column without a default?
A: You'll get this error. You must either provide a value, add a default, or remove the NOT NULL constraint.

Q: How do I check if a column is NOT NULL programmatically?
A: Query information_schema:

SELECT is_nullable
FROM information_schema.columns
WHERE table_name = 'users' AND column_name = 'email';

Q: Should I use NOT NULL for every column?
A: No, use it only for truly required fields. Optional fields should allow NULL unless there's a business reason for a default value.