Postgres: Create unique index with conditional

I have a table in Postgres with records of type [event_id, user_id, status]. The user is scoped per event, so I have a unique index on [event_id, user_id] so that a user has a unique status. The status is an enum type.

However, I can have only at most one record with status=owner.
I am trying to put a conditional index, something like [event_id, status] where "status" == "owner".

[ev, user, status]
[1,  1,    owner]   <- ok
[1,  2,    owner]   <- wrong
[1,  2,    pending] <- ok
[1,  3,    pending] <- ok

Any clue how to do this?

Yes, the syntax is almost what you imagined. They are called partial indexes (or filtered) and can be unique.

CREATE UNIQUE INDEX event_unique_ower_uqx    -- index name
    ON table_name (event_id) 
    WHERE (status = 'owner') ;

This essentially says:

"Don't allow more than one row with status 'owner'
and the same event_id."

This index poses no restrictions at all to any row with status different than 'owner'.

StackExchange Database Administrators Q#317508, answer score: 6