Partial index aka conditional index

[ndrean]

You can create a unique index with constraints. This should be declared in a changeset to capture the propgation of the error from the database

https://dba.stackexchange.com/questions/317508/postgres-create-unique-index-with-conditional/317510?noredirect=1#comment618954_317510

Example: given an "event_id", you want at most one record with status="owner"

[evt, user, status]
[1,  1,    owner]   <- ok
[1,  2,    owner]   <- not allowed
[1,  2,    pending] <- ok
[1,  3,    pending] <- ok

# migration
create unique_index(:event_participants, [:event_id],
             where: "status = 'owner'",
             name: :unique_owner
)

# schema
def changeset(%__MODULE__{} = event_participants, attrs) do
    event_participants
   [...]
   |> unique_constraint([:event_id], where: "status = 'owner'", name: :unique_owner)
end