Old, Good Database Design(relinx.io)

  -- 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;$$;