This approach is the best and works really well if you don't need to do a join on a related table to look up information. If you need to use data outside of the current table for exclusions/check constraints, you have to write a trigger function (as far as I know).
I had to solve this recently, where the actual start/end times were stored on a related table. I'm no SQL wizard, but I'd love to share my solution in case it helps others (it might be terrible).
Note: I changed the actual tables/domain to be generic, this is a poor example and it made more sense for my usecase, but this shows general approach.
-- Let's pretend we have these tables (awful design, but for sake of example):
-- room reservation reservation_info
-- Where "reservation_info" has "start_time" and "end_time"
CREATE FUNCTION check_for_overlapping_reservations()
RETURNS trigger
LANGUAGE plpgsql AS
$$ BEGIN
IF (
-- Find the newly created reservation and join it with the info record to grab "start_time" and "end_time" for check below
with this_reservation as (
select * from reservation
inner join reservation_info on reservation_info.id = reservation.reservation_info_id
where reservation.id = NEW.id
), bookings_for_timerange as (
-- Select every other reservation, where the reservation is happening in the same room
select * from reservation as other_reservation, this_reservation
inner join reservation_info as other_reservation_info
on other_reservation_info.id = other_reservation.reservation_info_id
where other_reservation.room_id = this_reservation.room_id AND
-- And the timerange from start to end overlaps the newly created record
tstzrange(this_reservation.start_time, this_reservation.end_time) &&
tstzrange(other_reservation_info.start_time, other_reservation_info.end_time)
-- Get a count of all the records, it should only be 1. If it's greater than one, there's overlap.
select count(*) from bookings_for_timerange
) > 1
THEN
RAISE EXCEPTION 'Room is already reserved during this time period';
END IF;
RETURN NEW;
END;$$;