SQL Date overlap avoidance inside of the CREATE statement using CHECK constraint-CodePudding

I am really stuck on this because I dont understand how can I check for possible overlaps in such cases. So this is the condition of the task: Imagine you are the manager of a hotel that has a table in its database with the following definition:

CREATE TABLE Hotel
(
    Room SMALLINT NOT NULL,
    Arrival DATE NOT NULL,
    Departure DATE NOT NULL,
    Guest CHAR(30),

    PRIMARY KEY (room number, arrival)
    CHECK (departure >= arrival)
);

So you can't leave this hotel before you've arrived. Now modify this definition so that no reservation can be entered in the table such as the Arrival/Departure date conflicts with an already existing reservation date. As overlaps count (examples):

already existing reservation 3.1.-6.1. with newly booked 1.1.‐5.1. or 4.1.-10.1.
or another existing 2.1.-6.1. with newly booked 1.1.-10.1. or 3.1.-5.1.

It also states, that it's OK to use selections from the Table inside of the CREATE statement of this same table, and use it in a CHECK constraint.

CodePudding user response：

Before reading check the tutorial about triggers and how they work https://www.postgresqltutorial.com/postgresql-triggers/creating-first-trigger-postgresql/


CREATE  TABLE if not exists Hotel
(
    Room SMALLINT NOT NULL,
    Arrival DATE NOT NULL,
    Departure DATE NOT NULL,
    Guest CHAR(30),

    PRIMARY KEY (room, arrival),
    CHECK (departure >= arrival)
); 
CREATE OR REPLACE FUNCTION register_new_insert()
  RETURNS TRIGGER 
  AS
$$
DECLARE
    r hotel%rowtype;
BEGIN
    FOR r IN
        SELECT * FROM hotel WHERE Room = New.Room;
    LOOP
      IF r.arrival < new.arrival and new.arrival < r.departure 
      THEN RAISE EXCEPTION 'Arrival/Departure date conflicts with an already existing reservation date';
      END IF;

      IF r.arrival < new.departure and new.departure < r.departure 
      THEN RAISE EXCEPTION 'Arrival/Departure date conflicts with an already existing reservation date';
      END IF;

    END LOOP;
    RETURN  NEW;
END;
$$ LANGUAGE PLPGSQL;

CREATE TRIGGER register_new
  BEFORE insert
  ON Hotel
  FOR EACH ROW
  EXECUTE PROCEDURE register_new_insert();

CodePudding user response：

You can create a daterange with existing arrival, depature columns and also with the new values, then apply the range overlaps operator (&&). (See demo);

create or replace function prevent_register_overlap()
  returns trigger 
 language plpgsql
as $$
begin
    if exists ( select null 
                  from hotel h 
                 where daterange(new. arrival, new.departure,'[]') &&
                       daterange(h.arrival, h.departure,'[]') 
                   and new.room = h.room
              ) 
          then raise exception 'Arrival/Departure overlaps existing reservation.';
   end if; 
   return new; 

end;
$$;

create trigger register_new
  before insert or update of arrival, departure 
  on hotel
  for each row
  execute procedure prevent_register_overlap();

As constructed these are closed ranges,both dates are included, see 8.17.6. Constructing Ranges for creating other types.