PostgreSQL 42P10: invalid column reference
Most often an ON CONFLICT target that no unique index matches, or ORDER BY with SELECT DISTINCT on a column that isn't selected.
- SQLSTATE
42P10- Name
invalid_column_reference
What it means
42P10 (invalid_column_reference) is raised when a column list refers to columns in a way the statement doesn't allow. The two cases people hit most:
INSERT ... ON CONFLICT (cols) needs a unique index or constraint on exactly those columns (or an exclusion constraint), so PostgreSQL knows what counts as a conflict. And with SELECT DISTINCT, every ORDER BY expression must appear in the select list: for SELECT DISTINCT, ORDER BY expressions must appear in select list.
Common causes
ON CONFLICT (email)on a table with no unique index onemail, or with a unique index onlower(email)or a different column set.- A partial unique index, which
ON CONFLICTonly uses when the statement repeats itsWHEREpredicate. SELECT DISTINCT a FROM t ORDER BY b.
How to fix it
-
Create the unique index that defines a conflict, or name the conflict target to match the existing one.
CREATE UNIQUE INDEX users_email_key ON users (email); INSERT INTO users (email, name) VALUES ('[email protected]', 'Ada') ON CONFLICT (email) DO UPDATE SET name = EXCLUDED.name; -
For an expression or partial index, repeat the expression or predicate in the conflict target.
CREATE UNIQUE INDEX users_email_lower_key ON users (lower(email)); INSERT INTO users (email) VALUES ('[email protected]') ON CONFLICT ((lower(email))) DO NOTHING; -
With
DISTINCT, select what you order by, or useDISTINCT ON.SELECT DISTINCT customer_id, created_at FROM orders ORDER BY created_at; -- or one row per customer, newest first: SELECT DISTINCT ON (customer_id) customer_id, created_at FROM orders ORDER BY customer_id, created_at DESC;
Reproduce it safely
CREATE TEMP TABLE u (email text);
INSERT INTO u VALUES ('a') ON CONFLICT (email) DO NOTHING; -- ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification
Related errors
23505duplicate key violates unique constraint42830invalid foreign key23P01exclusion constraint violation21000more than one row returned by a subquery
More syntax error or access rule violation errors
42601syntax error at or near42501permission denied42846cannot coerce42803column must appear in the GROUP BY clause42804datatype mismatch42P18could not determine data type of parameter42703column does not exist42883function does not exist
The code and message text are from the PostgreSQL documentation, Appendix A: PostgreSQL Error Codes. Browse the full PostgreSQL error list or search every database error.
Work on PostgreSQL in SQLly
A native SQL client for macOS, Windows and Linux with schema-aware IntelliSense, guardrails for risky statements and clear error messages, for PostgreSQL and the other databases you use.