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.
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.
The error message will tell you which column violates the NOT NULL constraint. Look for the column name in the error output:
ERROR: null value in column "username" violates not-null constraintIn this example, the "username" column is missing a value.
Query the table definition to see which columns have NOT NULL constraints:
\d your_table_nameOr use:
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.
Ensure your INSERT query includes all NOT NULL columns:
-- 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', '[email protected]', NOW());When updating data, use COALESCE to provide default values if NULL is encountered:
-- 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;If you're adding a new NOT NULL column to an existing table with data, provide a default value:
-- 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);If the error comes from your application:
// 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]
);
};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.
insufficient columns in unique constraint for partition key
How to fix "insufficient columns in unique constraint for partition key" in PostgreSQL
ERROR 42501: must be owner of table
How to fix "must be owner of table" in PostgreSQL
trigger cannot change partition destination
How to fix "Trigger cannot change partition destination" in PostgreSQL
SSL error: certificate does not match host name
SSL error: certificate does not match host name in PostgreSQL
No SSL connection
No SSL connection to PostgreSQL