4

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?

Improve this question

1 Answer 1

Reset to default
6

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'.

Improve this answer
6
  • This seems to stop also the (desired, allowed) duplicates of [event_id, status] when status != 'owner'. I will check what is wrong tomorrow. Commented Sep 28, 2022 at 0:02
  • Yes, you are right indeed, tested again. Thks Commented Sep 28, 2022 at 12:20
  • Quite powerfull! Commented Sep 28, 2022 at 12:31
  • OK, removing my comments Commented Sep 28, 2022 at 13:34
  • you shouldn't, your comment added something that I didn't know/understood Commented Sep 28, 2022 at 14:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.