scribase
SQLSTATE 23502not_null_violationHTTP 400

Postgres error 23502: not null violation

null value in column "x" of relation "y" violates not-null constraint

An INSERT or UPDATE left a NOT NULL column empty.

Common causes

  • The app did not send the column and it has no default.
  • An UPDATE set it to null explicitly.
  • A column added with NOT NULL but no default, then inserts from old code paths.

How to fix it

  • Send the value, or give the column a default (alter table ... alter column ... set default ...).
  • For ownership columns use a default such as auth.uid() so clients cannot forget it.
sql
alter table todos alter column user_id set default auth.uid();

Through a REST API

PostgREST, the REST layer behind supabase-js and Scribase, answers HTTP 400 for this error and returns the SQLSTATE in the code field of the JSON error body.

Questions

What does Postgres error 23502 mean?

23502 is not_null_violation in class 23 (Integrity Constraint Violation). An INSERT or UPDATE left a NOT NULL column empty.

How do I fix 23502?

Send the value, or give the column a default (alter table ... alter column ... set default ...). For ownership columns use a default such as auth.uid() so clients cannot forget it.

What HTTP status does a REST API return for 23502?

PostgREST, the REST layer behind supabase-js, answers 400.

Other class 23 errors

  • 23503 insert or update on table "x" violates foreign key constraint "x_y_fkey"
  • 23505 duplicate key value violates unique constraint "x_pkey"
  • 23514 new row for relation "x" violates check constraint "y"
  • 23P01 conflicting key value violates exclusion constraint
All Postgres error codesHosted Postgres with branching