How to fix ERROR: 23502: null value in column violates not-null constraint in PostgreSQL

PostgreSQLBEGINNERHIGHUpdated Dec 26, 2025· 272 views

This error occurs when an INSERT or UPDATE statement attempts to set a NULL value in a column declared with a NOT NULL constraint. PostgreSQL enforces data integrity by preventing null values in columns defined as NOT NULL. To fix this, provide a non-null value for the affected column or adjust the query to include all required fields.

Quick fix

Fixes 23502

ERROR: 23502: null value in column "user_id" of relation "orders" violates not-null constraint
DETAIL: Failing row contains (null, 100.00, 2024-01-15).
  1. Identify which column violates the constraint
  2. Provide a value for the NOT NULL column in INSERT
  3. Fix UPDATE statements that set columns to NULL
  4. Handle auto-increment columns correctly
postgresql — error
ERROR: 23502: null value in column "user_id" of relation "orders" violates not-null constraint
DETAIL: Failing row contains (null, 100.00, 2024-01-15).
ERROR: 23502: null value in column violates not-null constraint

What this error means

PostgreSQL error 23502 (not_null_violation) is an integrity constraint violation that occurs when you try to insert or update a row with a NULL value in a column that is explicitly defined as NOT NULL. A NOT NULL constraint ensures that a column must always contain a value - empty or undefined values are not permitted. This is a fundamental data integrity rule to prevent incomplete or invalid data in critical fields like IDs, timestamps, or user-provided information. The error occurs at the moment PostgreSQL attempts to execute the INSERT or UPDATE statement, preventing the problematic data from being written to the database. The error message typically includes the table name and the specific column that violated the constraint.

Typical symptoms

  • INSERT statements fail with 'ERROR: 23502: null value in column ... violates not-null constraint'
  • UPDATE queries raise the same 23502 error when setting a column to NULL
  • Application ORM (Sequelize, TypeORM, SQLAlchemy) throws validation error during save
  • Data migration scripts fail when populating a table with missing values
  • Batch inserts fail partway through when some rows lack required data
  • Error message specifies the exact table and column involved
  • Adding a NOT NULL constraint to an existing column containing NULLs fails

Common causes

  1. INSERT statement omits a column that has NOT NULL constraint and no DEFAULT value
  2. UPDATE statement explicitly sets a NOT NULL column to NULL
  3. Application code sends null or undefined values to the database without validation
  4. Data migration or CSV import does not provide values for all NOT NULL columns
  5. Auto-increment (SERIAL) column is set to NULL instead of being auto-generated
  6. JOIN or subquery returns NULL values that are then inserted into a NOT NULL column
  7. Trigger or stored procedure sets a NOT NULL column to NULL during insert/update
  8. Using ON CONFLICT UPDATE clause that sets NOT NULL columns to NULL

How to fix "ERROR: 23502: null value in column violates not-null constraint"

1Identify which column violates the constraint

The error message explicitly tells you the column name. Look for output like:

bash
ERROR: 23502: null value in column "user_id" of relation "orders" violates not-null constraint
DETAIL: Failing row contains (null, 100.00, 2024-01-15).

In this example, the user_id column in the orders table cannot be NULL.

If your error message is truncated, query the table schema:

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

Look for columns with is_nullable = 'NO' - these are your NOT NULL columns.

2Provide a value for the NOT NULL column in INSERT

WRONG (missing required column):

sql
INSERT INTO orders (order_total, order_date)
VALUES (99.99, '2024-01-15');
-- Fails if user_id is NOT NULL

CORRECT (include all NOT NULL columns):

sql
INSERT INTO orders (user_id, order_total, order_date)
VALUES (42, 99.99, '2024-01-15');

If you have multiple rows:

sql
INSERT INTO orders (user_id, order_total, order_date) VALUES
(42, 99.99, '2024-01-15'),
(43, 150.00, '2024-01-15'),
(44, 75.50, '2024-01-15');

Make sure every NOT NULL column gets a value in every row.

3Fix UPDATE statements that set columns to NULL

WRONG (sets NOT NULL column to NULL):

sql
UPDATE orders
SET user_id = NULL
WHERE order_id = 123;
-- Fails because user_id is NOT NULL

CORRECT (use a valid value instead):

sql
UPDATE orders
SET user_id = 1  -- system default user
WHERE order_id = 123;

Or if you're trying to clear optional data, use a different column:

sql
UPDATE orders
SET notes = NULL,  -- notes is nullable
    updated_at = NOW()
WHERE order_id = 123;

To find which columns ARE nullable:

sql
SELECT column_name
FROM information_schema.columns
WHERE table_name = 'orders' AND is_nullable = 'YES';
4Handle auto-increment columns correctly

If the NOT NULL column is a PRIMARY KEY or SERIAL auto-increment:

WRONG (explicitly passing NULL to SERIAL):

sql
INSERT INTO users (id, username, email)
VALUES (NULL, 'alice', '[email protected]');
-- Fails even though SERIAL auto-generates id

CORRECT (omit the SERIAL column to auto-generate):

sql
INSERT INTO users (username, email)
VALUES ('alice', '[email protected]');
-- id is auto-generated as next sequence value

If you need to specify a particular id:

sql
INSERT INTO users (id, username, email)
VALUES (100, 'alice', '[email protected]');
-- Explicitly provide non-NULL id

Check your column definition:

sql
SELECT column_name, data_type, column_default
FROM information_schema.columns
WHERE table_name = 'users' AND column_name = 'id';

If column_default contains 'nextval', it's SERIAL and should be omitted from INSERT.

5Validate data before inserting via application code

Most modern ORMs can validate NOT NULL constraints before executing SQL:

JavaScript/TypeScript (Prisma):

typescript
const order = await prisma.orders.create({
  data: {
    userId: 42,        // Required - NOT NULL
    orderTotal: 99.99,
    orderDate: new Date(),
  },
});
// Prisma's type system enforces userId is required

Python (SQLAlchemy):

python
order = Order(
    user_id=42,        # Required
    order_total=99.99,
    order_date=datetime.now()
)
session.add(order)
session.commit()  # Validates before commit

Ruby (ActiveRecord):

ruby
order = Order.new(
  user_id: 42,       # Required
  order_total: 99.99,
  order_date: Time.now
)
order.save!  # Raises if validation fails

Add application-level validation:

typescript
function validateOrder(data: any) {
  if (!data.userId) {
    throw new Error('user_id is required');
  }
  if (!data.orderTotal) {
    throw new Error('order_total is required');
  }
  return true;
}

validateOrder(orderData);
const result = await insertOrder(orderData);
6Use DEFAULT values for columns that might be NULL

If a column should always have a value but is sometimes missing, define a DEFAULT:

Add a DEFAULT to an existing column:

sql
ALTER TABLE orders
ALTER COLUMN status SET DEFAULT 'pending';

Define DEFAULT when creating a table:

sql
CREATE TABLE orders (
  id SERIAL PRIMARY KEY,
  user_id INT NOT NULL,
  order_total DECIMAL(10, 2) NOT NULL,
  status VARCHAR(50) NOT NULL DEFAULT 'pending',
  created_at TIMESTAMP NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMP DEFAULT NOW()
);

Now INSERT without specifying status or created_at:

sql
INSERT INTO orders (user_id, order_total)
VALUES (42, 99.99);
-- status defaults to 'pending'
-- created_at defaults to current timestamp
7Handle NULL values from JOINs or subqueries

When copying data from other tables, NULL values may propagate:

WRONG (NULL from JOIN):

sql
INSERT INTO archive (user_id, amount)
SELECT u.id, o.amount
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.status = 'inactive';
-- Fails if u.id is NULL (unlikely) or o.amount is NULL

CORRECT (use COALESCE to provide defaults):

sql
INSERT INTO archive (user_id, amount)
SELECT u.id, COALESCE(o.amount, 0)
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.status = 'inactive'
  AND u.id IS NOT NULL;

Or filter out rows with missing required data:

sql
INSERT INTO archive (user_id, amount)
SELECT u.id, o.amount
FROM users u
INNER JOIN orders o ON u.id = o.user_id
WHERE u.status = 'inactive'
  AND o.amount IS NOT NULL;

COALESCE returns the first non-NULL value:

sql
COALESCE(column, default_value)
COALESCE(column1, column2, 'fallback')
8Migrate existing NULL data before adding NOT NULL constraint

If you're trying to add a NOT NULL constraint to an existing column that contains NULLs:

WRONG (fails immediately):

sql
ALTER TABLE orders
ALTER COLUMN user_id SET NOT NULL;
-- Fails if any rows have user_id = NULL

CORRECT (update NULLs first):

sql
-- Step 1: Check how many NULLs exist
SELECT COUNT(*) FROM orders WHERE user_id IS NULL;

-- Step 2: Update NULLs to a valid value
UPDATE orders
SET user_id = 1  -- default/fallback user id
WHERE user_id IS NULL;

-- Step 3: Verify all rows now have values
SELECT COUNT(*) FROM orders WHERE user_id IS NULL;

-- Step 4: Now add the constraint
ALTER TABLE orders
ALTER COLUMN user_id SET NOT NULL;

If you're adding a new NOT NULL column to an existing table:

sql
-- Add column with temporary DEFAULT
ALTER TABLE orders
ADD COLUMN status VARCHAR(50) NOT NULL DEFAULT 'pending';

-- PostgreSQL applies DEFAULT to existing rows
-- No need for UPDATE if DEFAULT is sufficient

-- Later, you can remove DEFAULT if desired
ALTER TABLE orders
ALTER COLUMN status DROP DEFAULT;

Advanced notes

Understanding NOT NULL Constraints:

NOT NULL is one of PostgreSQL's most basic integrity constraints. Unlike most constraints, it applies at the row level for every INSERT/UPDATE, making it a first-line defense against incomplete data.

Constraint vs Default:

A NOT NULL constraint (enforces rule) is different from a DEFAULT (provides automatic value):

sql
-- NOT NULL alone: value REQUIRED
CREATE TABLE users (
  id INT NOT NULL
);
-- INSERT requires explicit value for id

-- NOT NULL with DEFAULT: value optional (uses default if omitted)
CREATE TABLE users (
  id SERIAL NOT NULL DEFAULT nextval('users_id_seq'),
  created_at TIMESTAMP NOT NULL DEFAULT NOW()
);
-- INSERT can omit both; defaults are used

-- Nullable with DEFAULT: value optional
CREATE TABLE users (
  deleted_at TIMESTAMP DEFAULT NULL
);
-- INSERT can omit; NULL becomes default

Composite Keys and NOT NULL:

All columns in a composite PRIMARY KEY are implicitly NOT NULL:

sql
CREATE TABLE order_items (
  order_id INT NOT NULL,
  product_id INT NOT NULL,
  quantity INT NOT NULL,
  PRIMARY KEY (order_id, product_id)
);
-- All three columns are required

Performance Note:

NOT NULL constraints have no performance penalty. In fact, they enable better query optimization because PostgreSQL knows the column always has a value. Nullable columns require extra checking in some operations.

Common NOT NULL Pattern:

Most production schemas use this pattern:

sql
CREATE TABLE orders (
  id BIGSERIAL PRIMARY KEY,
  user_id BIGINT NOT NULL REFERENCES users(id),
  amount DECIMAL(10, 2) NOT NULL,
  status VARCHAR(50) NOT NULL DEFAULT 'pending',
  notes TEXT,  -- nullable for optional data
  created_at TIMESTAMP NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMP NOT NULL DEFAULT NOW()
);

Tools to Check Constraints:

sql
-- List all constraints on a table
SELECT constraint_name, constraint_type
FROM information_schema.table_constraints
WHERE table_name = 'orders';

-- Check specific column nullability
SELECT column_name, is_nullable
FROM information_schema.columns
WHERE table_name = 'orders'
ORDER BY ordinal_position;

Related Errors:

- 23503 (foreign_key_violation): Referenced row doesn't exist
- 23505 (unique_violation): Duplicate value in unique column
- 23514 (check_violation): Value fails CHECK constraint

Frequently asked questions

What does "23502" mean?
PostgreSQL error 23502 (not_null_violation) is an integrity constraint violation that occurs when you try to insert or update a row with a NULL value in a column that is explicitly defined as NOT NULL. A NOT NULL constraint ensures that a column must always contain a value - empty or undefined values are not permitted. This is a fundamental data integrity rule to prevent incomplete or invalid data in critical fields like IDs, timestamps, or user-provided information.
How do I fix "23502"?
This error occurs when an INSERT or UPDATE statement attempts to set a NULL value in a column declared with a NOT NULL constraint. PostgreSQL enforces data integrity by preventing null values in columns defined as NOT NULL. To fix this, provide a non-null value for the affected column or adjust the query to include all required fields.
What causes "23502"?
INSERT statement omits a column that has NOT NULL constraint and no DEFAULT value UPDATE statement explicitly sets a NOT NULL column to NULL Application code sends null or undefined values to the database without validation
Is the "23502" 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