// PostgreSQL error reference

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
Category
Syntax Error or Access Rule Violation
Error message
there is no unique or exclusion constraint matching the ON CONFLICT specification

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 on email, or with a unique index on lower(email) or a different column set.
  • A partial unique index, which ON CONFLICT only uses when the statement repeats its WHERE predicate.
  • SELECT DISTINCT a FROM t ORDER BY b.

How to fix it

  1. 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;
  2. 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;
  3. With DISTINCT, select what you order by, or use DISTINCT 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

More syntax error or access rule violation errors

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.

// fewer errors, faster fixes

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.