Hacker News Reader

Another benefit of partial indexes is to limit a unique constraint:

create index users_email on users(email) where status != 'delete'

Be very careful, then, as the optimizer will (usually?) not use the index if the condition is not part if the query.

you can create views for that, then it will be always part of the query.