Fix PostgreSQL 'null value in column violates not-null constraint' Error

The Error

You fire off an INSERT or UPDATE and PostgreSQL stops you cold:

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

The message is blunt but useful. You tried to write NULL into user_id, and that column has a NOT NULL constraint — it demands a real value, every single time, no exceptions.

Why This Happens

Several scenarios trigger this. The most common ones:

• Your INSERT omits the column entirely and no DEFAULT is defined — PostgreSQL has nothing to fall back on.
• Application code passes None / null / undefined straight to the query without any validation beforehand.
• Someone added a NOT NULL constraint to an existing column, but older app code never learned about it and still skips the field.
• An ORM model or migration script has drifted out of sync with the actual table schema.

Step 1 — Check the Table Schema

Before touching anything, confirm which columns actually enforce NOT NULL. In psql:

\d orders

Or use this query — it works in every client, including pgAdmin and DBeaver:

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

Focus on rows where is_nullable = 'NO' and column_default is empty. Those columns need an explicit value on every insert — no default to save you.

Step 2 — Find the Bug in Your Statement

Put your INSERT side by side with the schema output. Two patterns cause most of these errors.

The first: omitting the column entirely:

-- user_id is missing — PostgreSQL receives NULL for it
INSERT INTO orders (product_id, order_date)
VALUES (101, '2024-01-15');

The second: passing an explicit NULL:

INSERT INTO orders (user_id, product_id, order_date)
VALUES (NULL, 101, '2024-01-15');  -- rejected immediately

Either way, PostgreSQL sees NULL landing in a column that won't accept it.

Step 3 — Choose a Fix

Option A: Supply the missing value (covers ~80% of cases)

Just include the required column with a real value:

INSERT INTO orders (user_id, product_id, order_date)
VALUES (42, 101, '2024-01-15');

When inserting from application code, validate before the query runs — not after:

# Python example
if user_id is None:
    raise ValueError("user_id is required before inserting an order")

cursor.execute(
    "INSERT INTO orders (user_id, product_id, order_date) VALUES (%s, %s, %s)",
    (user_id, product_id, order_date)
)

Option B: Add a DEFAULT to the column

Got a sensible fallback? For example, automated system orders might always belong to user 0. Set it once on the column:

ALTER TABLE orders
  ALTER COLUMN user_id SET DEFAULT 0;

Now any INSERT that omits user_id quietly uses 0 instead of blowing up.

Option C: Drop the NOT NULL constraint (only when NULL is genuinely valid)

Guest checkouts are a real example — an order might legitimately have no associated user. If that's your situation:

ALTER TABLE orders
  ALTER COLUMN user_id DROP NOT NULL;

Don't do this just to silence the error. Do it only when NULL carries actual meaning in your data model.

Option D: Back-fill existing rows before enforcing NOT NULL

Adding a new NOT NULL column to a table that already has rows? Those rows will have NULL and block the migration. Back-fill first, constrain second:

-- Step 1: add the column, allow NULL for now
ALTER TABLE orders ADD COLUMN user_id INTEGER;

-- Step 2: fill in a real value for all existing rows
UPDATE orders SET user_id = 0 WHERE user_id IS NULL;

-- Step 3: lock it down
ALTER TABLE orders ALTER COLUMN user_id SET NOT NULL;

Skipping Step 2 is the single most common migration mistake with this error.

Step 4 — Verify the Fix

Re-run your original statement with RETURNING * tacked on:

INSERT INTO orders (user_id, product_id, order_date)
VALUES (42, 101, '2024-01-15')
RETURNING *;

PostgreSQL returns the inserted row on success. No error means the constraint is satisfied. Double-check the data landed correctly:

SELECT * FROM orders WHERE product_id = 101 ORDER BY order_date DESC LIMIT 5;

Preventing This Going Forward

• Validate before the query, not inside it — reject None/null at the application layer. By the time a value reaches the database, it should already be clean.
• Always use RETURNING in inserts — it confirms the write succeeded and shows exactly what was stored. Cheap insurance.
• Back-fill before you constrain — adding NOT NULL to a live table without a default will fail instantly if any existing row has NULL there. The three-step migration above is the safe pattern.
• Keep ORM models in sync — after every migration, verify that SQLAlchemy, Django ORM, or Prisma models reflect the actual schema. Drift between model and table is a silent bug factory.
• Read the error message — PostgreSQL hands you the column name ("user_id") and the table ("orders") right in the error. Go straight to that column instead of scanning the whole schema.