How to fix Null value not allowed in PostgreSQL

PostgreSQLBEGINNERHIGHUpdated Dec 26, 2025· 250 views

A NOT NULL constraint violation occurs when you try to insert or update a record with a NULL value in a column that requires a value. Learn how to identify the problematic column and fix the query.

Quick fix

Fixes Null value not allowed

ERROR: null value in column "username" violates not-null constraint
  1. Identify the problematic column
  2. Check the table schema
  3. Update your INSERT statement
  4. Fix UPDATE statements with COALESCE
postgresql — error
ERROR: null value in column "username" violates not-null constraint
Null value not allowed

What this error means

In PostgreSQL, a NOT NULL constraint enforces that a column must always contain a value. When you attempt to insert or update a row without providing a value for a NOT NULL column, the database rejects the operation and returns this error. This constraint is a safeguard to ensure data integrity and prevent incomplete or invalid records from being stored in your database.

Typical symptoms

  • INSERT or UPDATE queries fail with NOT NULL constraint error
  • Application encounters database errors during data modification operations
  • Specific column names are mentioned in the error message indicating which field is missing a value
  • Transactions are rolled back entirely when this constraint is violated

Common causes

  1. Missing values in INSERT statements for NOT NULL columns
  2. Omitting a required column from an INSERT query
  3. Passing NULL explicitly or implicitly to a NOT NULL column in UPDATE statements
  4. Triggers or application logic that modifies data and introduces NULL values
  5. Stored procedures or functions that return NULL for required fields
  6. Data migration scripts that fail to provide values for newly added NOT NULL columns

How to fix "Null value not allowed"

1Identify the problematic column

The error message will tell you which column violates the NOT NULL constraint. Look for the column name in the error output:

bash
ERROR: null value in column "username" violates not-null constraint

In this example, the "username" column is missing a value.

2Check the table schema

Query the table definition to see which columns have NOT NULL constraints:

sql
\d your_table_name

Or use:

sql
SELECT column_name, is_nullable, data_type
FROM information_schema.columns
WHERE table_name = 'your_table_name'
ORDER BY ordinal_position;

Columns with is_nullable = false cannot accept NULL values.

3Update your INSERT statement

Ensure your INSERT query includes all NOT NULL columns:

sql
-- Wrong - missing "username" and "email"
INSERT INTO users (id, created_at)
VALUES (1, NOW());

-- Correct - all NOT NULL columns included
INSERT INTO users (id, username, email, created_at)
VALUES (1, 'john_doe', 'john@example.com', NOW());
4Fix UPDATE statements with COALESCE

When updating data, use COALESCE to provide default values if NULL is encountered:

sql
-- Before (may fail if column contains NULL)
UPDATE users
SET username = COALESCE(username, 'unknown')
WHERE id = 1;

-- Or use CASE for conditional logic
UPDATE users
SET username = CASE
  WHEN username IS NULL THEN 'default_user'
  ELSE username
END
WHERE id = 1;
5Add defaults to new NOT NULL columns

If you're adding a new NOT NULL column to an existing table with data, provide a default value:

sql
-- Correct - provides default for existing rows
ALTER TABLE users
ADD COLUMN status VARCHAR(50) NOT NULL DEFAULT 'active';

-- Update any NULL values first if migrating
UPDATE users SET status = 'active' WHERE status IS NULL;

-- Then add the constraint
ALTER TABLE users
ADD CONSTRAINT status_not_null CHECK (status IS NOT NULL);
6Review application code

If the error comes from your application:

typescript
// Wrong - undefined gets treated as NULL
const insertUser = async (user: any) => {
  const result = await db.query(
    'INSERT INTO users (id, username, email) VALUES ($1, $2, $3)',
    [user.id, user.username, user.email] // email might be undefined
  );
};

// Correct - validate before inserting
const insertUser = async (user: User) => {
  if (!user.email) throw new Error('Email is required');
  const result = await db.query(
    'INSERT INTO users (id, username, email) VALUES ($1, $2, $3)',
    [user.id, user.username, user.email]
  );
};

Advanced notes

If the error occurs in a transaction, the entire transaction is rolled back. Use savepoints to partially roll back failed operations without losing all work. For ORMs like Prisma or TypeORM, ensure your data models match the database schema and required fields are properly validated before database operations. When migrating databases or adding new columns, always test with sample data first to ensure constraints are satisfied.

Frequently asked questions

What does "Null value not allowed" mean?
In PostgreSQL, a NOT NULL constraint enforces that a column must always contain a value. When you attempt to insert or update a row without providing a value for a NOT NULL column, the database rejects the operation and returns this error. This constraint is a safeguard to ensure data integrity and prevent incomplete or invalid records from being stored in your database.
How do I fix "Null value not allowed"?
A NOT NULL constraint violation occurs when you try to insert or update a record with a NULL value in a column that requires a value. Learn how to identify the problematic column and fix the query.
What causes "Null value not allowed"?
Missing values in INSERT statements for NOT NULL columns Omitting a required column from an INSERT query Passing NULL explicitly or implicitly to a NOT NULL column in UPDATE statements
Is the "Null value not allowed" error serious?
Yes. This is a high-severity error that usually blocks work until it is fixed, but the steps above resolve it.

Official resources & further reading