exclude using gist from another table?

I have a users, reservations table, and a reservations_users table, I’m using uuid-ossp and btree_gist extenstions on postgres 14.

I want multiple users to be a part of a reservation, e.g. they reserve a hotel together as two different users etc (this part doesn’t matter), so I added a reservations_users table to allow for this, and I belive this is correct, and here’s the SQL for that:

The code: “`sql create extension if not exists “uuid-ossp”; create extension if not exists “btree_gist”;

create table users ( id uuid primary key default uuid_generate_v4() );

create table reservations ( id uuid primary key default uuid_generate_v4(), reservation_timestamp_range tsrange — reservation start and end time range );

create table reservations_users ( reservation_id uuid references reservations(id), user_id uuid references users(id), primary key (reservation_id, user_id) ); “`

The problem: is, I want to create a constraint on reservation_timestamp_range to prevent overlapping time ranges for a given user on a reservation to prevent the same user making multiple reservations at once, and not sure how to go about it.

When I originally had just one user per reservation, I had the following and it worked:

sql alter table reservations add constraint no_overlapping_reservations_for_users exclude using gist (user_id with =, reservation_timestamp_range with &&);

So, is it possible to use the exclude using gist from one table to another? I’ve tried a few things specifically with exclude but I’m not sure if it’s even possible.

What I tried:: sql alter table reservations_users add constraint no_overlapping_reservations_for_users exclude using gist (user_id with =, reservation_timestamp_range with &&) where (user_id in (select user_id from reservations_users where reservation_id = reservations.id)); which returns the following error: ERROR: cannot use subquery in index predicate LINE 4: where (user_id in (select user_id from reservations_...

So I tried a different approach: sql alter table reservations_users add constraint no_overlapping_reservations_for_users exclude using gist (user_id with =, reservation_timestamp_range with &&) where (select user_id from reservations_users ru where ru.user_id = reservations_users.user_id) is not null; Then I tried to do this instead: sql alter table reservations_users add constraint no_overlapping_reservations_for_users exclude using gist (user_id with =, reservation_timestamp_range with &&) where (select count(*) from reservations_users ru where ru.user_id = reservations_users.user_id) > 0;

Both of which return the following error: ERROR: syntax error at or near "select" LINE 4: where (select count(*) from reservations_users ru where ru...

Is this even possible? I’m not sure if I’m going about this the right way, or if I’m even using the right syntax, or if I’m just missing something obvious. Thanks in advance for any help.