The bug I split a 3-column unique constraint into two partial unique indexes to support a...
PostgREST upsert() can't handle partial unique indexes because ON CONFLICT doesn't support WHERE clause matching. When using nullable-column uniqueness rules, replace upsert with delete-then-insert at application level, accepting higher latency.
The bug
I split a 3-column unique constraint into two partial unique indexes to support a new nullable column. The next day, every .upsert() against that table started failing with:
there is no unique or exclusion constraint matching the ON CONFLICT specification
Enter fullscreen mode
Exit fullscreen mode
-- looks fine in review, locks your busiest table in prod: ALTER TABLE users ALTER COLUMN email SET...
I've always been amazed by how Oracle Database handles updates to a unique column—performing...
Your Test Data Is Type-Correct and Still Invalid: 6 Postgres Schema Features Generators...
SQLite Ecosystem: RTree/JSON Bugs, ON CONFLICT DO SELECT & PG Query Planning ...
-- looks routine in review, locks your table in production: CREATE INDEX idx_orders_customer ON...
TL;DR I use two identifiers per row: a UUID as the public ID (URLs, APIs, forms) and an...
