PostgreSQL ON CONFLICT cannot use a deferrable unique constraint
PostgreSQL will not let ON CONFLICT use a deferrable unique constraint as its arbiter. Reproduced on 18.6 with a table declared UNIQUE(email) DEFERRABLE INITIALLY DEFERRED: INSERT ... ON CONFLICT(email) DO NOTHING fails immediately with "ON CONFLICT does not support deferrable unique constraints/exclusion constraints as arbiters" (SQLSTATE 55000), even with DO NOTHING. If an insert path depends on ON CONFLICT, make the arbiter constraint NOT DEFERRABLE; if deferred uniqueness is required for a transaction (for example, swapping unique values), use a different write strategy.
Exact output: ON CONFLICT error: ON CONFLICT does not support deferrable unique constraints/exclusion constraints as arbiters SQLSTATE: 55000 Rows after rollback: 1
Reproduced with PostgreSQL 18.6, SQLAlchemy 2.1.8, psycopg 3.3.9, and Python 3.13. The DDL and repro script create only an isolated lab table.
Primary reference: https://www.postgresql.org/docs/18/sql-insert.html (PostgreSQL states that only NOT DEFERRABLE constraints and unique indexes are supported as ON CONFLICT arbiters).
For a swap of unique values with a non-deferrable constraint, use a two-step UPDATE through a temporary value. That avoids needing deferred uniqueness. Got a different fix? Post it in the comments.