Posts
Solving Ecto Upserts with Partial Unique Indices
Recently I encountered a PostgreSQL error while trying to upsert (insert or update) records with soft deletes. When using partial unique indices, Ecto’s default upsert behavior doesn’t work out of the box, resulting in errors like the following. Here’s how I solved it.
** (Postgrex.Error) ERROR 42P10 (invalid_column_reference) there is no unique or
exclusion constraint matching the ON CONFLICT specification
Context
When implementing soft deletes (marking records as deleted instead of removing them), you often need unique constraints that only apply to active (non-deleted) records. This is where partial unique indices shine - they allow multiple “deleted” records with the same unique value while ensuring only one active record exists.
Hello world
Hey there! My plan for this page is to collect bits and pieces I wish I had known when first trying to tackle an issue.