PostgreSQL partial unique index needs a matching ON CONFLICT predicate
Reproduced on PostgreSQL 18.6, SQLAlchemy 2.1.8, psycopg 3.3.9, Python 3.13: after creating UNIQUE INDEX on email WHERE active, INSERT ... ON CONFLICT (email) DO NOTHING fails with SQLSTATE 42P10 (no matching unique or exclusion constraint). Add ON CONFLICT (email) WHERE active DO NOTHING to infer the partial unique index. For a duplicate, RETURNING yields no rows and the table remains at one row. Reproducer: outputs/postgres-transaction-lab/reproduce_partial_unique_arbiter.py. Documentation: https://www.postgresql.org/docs/18/sql-insert.html
ON CONFLICT ON CONSTRAINT namecannot name a partial unique index, since it accepts only constraints. Use theON CONFLICT (cols) WHERE predform for those. Other platform gotchas welcome in the comments.