Demonstrate how to use EXCLUDE to prevent overlapping time sequences.

postgres_exclude_constraint.sql

-- Create 2 tables to link together
CREATE TABLE customer (id SERIAL PRIMARY KEY);
CREATE TABLE article (id SERIAL PRIMARY KEY);

-- Insert a few rows
INSERT INTO customer DEFAULT VALUES;
INSERT INTO customer DEFAULT VALUES;
INSERT INTO article DEFAULT VALUES;
INSERT INTO article DEFAULT VALUES;

-- This extension is needed for the timestamp comparison
CREATE EXTENSION btree_gist;

-- Create the m:m relation table
CREATE TABLE rentals (
    id SERIAL PRIMARY KEY,
    customer_id INTEGER NOT NULL REFERENCES customer(id),
    article_id INTEGER NOT NULL REFERENCES article(id),
    rented_at TIMESTAMPTZ NOT NULL,
    returned_at TIMESTAMPTZ,

    -- Rows that have the same article and overlapping rental-periods will be invalid
    -- Ranges that end with NULL go on forever thus excludes all future rentals
    EXCLUDE USING gist (
        article_id WITH =,
        tstzrange(rented_at, returned_at) WITH &&
    )
);

-- Insert a valid ended rental
INSERT INTO rentals (customer_id, article_id, rented_at, returned_at) VALUES (1, 1, current_timestamp, current_timestamp);

-- Insert a valid ongoing rental
INSERT INTO rentals (customer_id, article_id, rented_at, returned_at) VALUES (1, 1, current_timestamp, NULL);

-- SELECT * FROM rentals;
--  customer_id | article_id |           rented_at           |          returned_at
-- -------------+------------+-------------------------------+-------------------------------
--            1 |          1 | 2017-01-11 15:07:47.584307+01 | 2017-01-11 15:07:47.584307+01
--            1 |          1 | 2017-01-11 15:07:52.836685+01 | (null)
-- (2 rows)


-- Try to insert another ongoing rental
INSERT INTO rentals (customer_id, article_id, rented_at, returned_at) VALUES (1, 1, current_timestamp, NULL);
-- ERROR:  conflicting key value violates exclusion constraint "rentals_article_id_tstzrange_excl"

postgres_trigger.sql

-- Create 2 tables to link together
DROP TABLE IF EXISTS customer;
DROP TABLE IF EXISTS article;
DROP TABLE IF EXISTS rentals;
DROP FUNCTION check_double_rental() CASCADE;

-- An example accomplishing almost the same with a trigger

-- Create 2 tables to link together
CREATE TABLE customer (id SERIAL PRIMARY KEY);
CREATE TABLE article (id SERIAL PRIMARY KEY);

-- Insert a few rows
INSERT INTO customer DEFAULT VALUES;
INSERT INTO customer DEFAULT VALUES;
INSERT INTO article DEFAULT VALUES;
INSERT INTO article DEFAULT VALUES;

-- Create the m:m relation table
CREATE TABLE rentals (
    id SERIAL PRIMARY KEY,
    customer_id INTEGER NOT NULL REFERENCES customer(id),
    article_id INTEGER NOT NULL REFERENCES article(id),
    rented_at TIMESTAMPTZ NOT NULL,
    returned_at TIMESTAMPTZ
);


-- Check for double rentals using a trigger
CREATE FUNCTION check_double_rental() RETURNS trigger AS $$
    BEGIN
        IF NEW.returned_at IS NULL AND EXISTS(SELECT 1 FROM rentals WHERE article_id = NEW.article_id AND returned_at IS NULL) THEN
            RAISE EXCEPTION 'cannot rent article, already rented out';
        END IF;

        RETURN NEW;
    END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER check_rented_twice BEFORE INSERT OR UPDATE ON rentals
    FOR EACH ROW EXECUTE PROCEDURE check_double_rental();



-- Insert a valid ended rental
INSERT INTO rentals (customer_id, article_id, rented_at, returned_at) VALUES (1, 1, current_timestamp, current_timestamp);

-- Insert a valid ongoing rental
INSERT INTO rentals (customer_id, article_id, rented_at, returned_at) VALUES (1, 1, current_timestamp, NULL);

-- SELECT * FROM rentals;
--  customer_id | article_id |           rented_at           |          returned_at
-- -------------+------------+-------------------------------+-------------------------------
--            1 |          1 | 2017-01-11 15:46:15.084066+01 | 2017-01-11 15:46:15.084066+01
--            1 |          1 | 2017-01-11 15:46:17.926094+01 | (null)
-- (2 rows)


-- Try to insert another ongoing rental
INSERT INTO rentals (customer_id, article_id, rented_at, returned_at) VALUES (1, 1, current_timestamp, NULL);
-- ERROR:  cannot rent article, already rented out
-- CONTEXT:  PL/pgSQL function check_double_rental() line 4 at RAISE