Exclude overlapping events (start/end) while considering the venue

I have the following table:

events
- id
- venue_id
- starts_at
- ends_at

I found this constraint https://www.postgresql.org/docs/current/sql-createtable.html#SQL-CREATETABLE-EXCLUDE but I'm not sure I understand how I can use it to prevent the creation of overlapping events while considering the venue_id so that there can be overlapping events, but not for the same venues.

for example:

EXCLUDE USING gist (tsrange(starts_at, ends_at) and somehow consider venue_id

Thanks

CodePudding user response：

You need to combine the "overlapping" of the timestamp range with an = operator on the venue_id

alter table events 
   add constraint no_overlapping_events
   exclude using gist (venue_id with =, tsrange(starts_at, ends_at) with &&)

Note that this requires the bree_gist extension due to the use of the = operator