Postgres error 42P10: invalid column reference
there is no unique or exclusion constraint matching the ON CONFLICT specification
An upsert names conflict columns that do not have a unique index or constraint.
Common causes
- ON CONFLICT (email) without a unique index on email.
- A unique index that is partial or uses an expression (lower(email)) that the ON CONFLICT clause does not repeat.
How to fix it
- Create the unique index: create unique index on users (email);
- Repeat the index expression or predicate in ON CONFLICT ((lower(email))) or ON CONFLICT (...) WHERE ....
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. In supabase-js, .upsert(rows, { onConflict: 'email' }) needs a unique index on email.
Questions
What does Postgres error 42P10 mean?
42P10 is invalid_column_reference in class 42 (Syntax Error or Access Rule Violation). An upsert names conflict columns that do not have a unique index or constraint.
How do I fix 42P10?
Create the unique index: create unique index on users (email); Repeat the index expression or predicate in ON CONFLICT ((lower(email))) or ON CONFLICT (...) WHERE ....
What HTTP status does a REST API return for 42P10?
PostgREST, the REST layer behind supabase-js, answers 400.