null value in column violates not-null constraint

This error occurs when you try to insert or update a NULL value into a column that has a NOT NULL constraint.

The null value in column violates not-null constraint error means you're trying to insert NULL where it's not allowed.

Understanding the Error

ERROR: null value in column "email" of relation "users" violates not-null constraint
DETAIL: Failing row contains (1, null, Alice).

The column requires a value and NULL was provided.

Common Causes

1. Missing Required Field

SQL

-- email is NOT NULL but not provided
INSERT INTO users (name) VALUES ('Alice');  -- Error if email has no default

2. Explicit NULL Value

SQL

INSERT INTO users (email, name) VALUES (NULL, 'Alice');  -- Error!

3. Application Passing NULL

JAVASCRIPT

// JavaScript variable is undefined/null
const email = undefined;
await db.query('INSERT INTO users (email, name) VALUES ($1, $2)', [email, 'Alice']);

4. Missing Default Value

SQL

-- Column has no default, NULL not allowed
ALTER TABLE users ADD COLUMN status VARCHAR(20) NOT NULL;  -- Error if table has data

How to Fix It

Solution 1: Provide the Required Value

SQL

INSERT INTO users (email, name) VALUES ('alice@example.com', 'Alice');

Solution 2: Add a Default Value

SQL

-- Add default when creating column
ALTER TABLE users ADD COLUMN status VARCHAR(20) NOT NULL DEFAULT 'active';

-- Or modify existing column
ALTER TABLE users ALTER COLUMN status SET DEFAULT 'active';

Solution 3: Use COALESCE

SQL

INSERT INTO users (email, name)
VALUES (COALESCE($1, 'default@example.com'), 'Alice');

Solution 4: Remove NOT NULL Constraint

SQL

-- If NULL should be allowed
ALTER TABLE users ALTER COLUMN email DROP NOT NULL;

Solution 5: Validate in Application

JAVASCRIPT

function createUser(email, name) {
  if (!email) {
    throw new Error('Email is required');
  }
  return db.query('INSERT INTO users (email, name) VALUES ($1, $2)', [email, name]);
}

Solution 6: Add Column with Default for Existing Data

SQL

-- When adding NOT NULL column to existing table
ALTER TABLE users ADD COLUMN created_at TIMESTAMP NOT NULL DEFAULT NOW();

Best Practices

Set sensible defaults for NOT NULL columns
Validate input before database operations
Use COALESCE for optional values with fallbacks
Consider NULLable columns for truly optional data
Document required fields in your schema