Postgres: Check if two boxes overlap

Question

I currently have the following tables in a database:

create table map (
    id          bigint          not null unique,
    zone        box             not null,
    ...
    primary key(id)
);

create table other_map (
    id          bigint          not null unique,
    zone        box             not null,
    ...
    primary key(id),
    foreign key(id) references map(id)
);

I don't want to allow a new row to be inserted in other_map if there is a row in map whose id is equal to the new entry's id and their zone attributes overlap. I found this answer, which explains how to detect overlapping boxes, but I'd like to know how to (best) apply that in Postgres.

This is what I've come up with so far, using a trigger and a stored procedure:

CREATE OR REPLACE FUNCTION PROC_other_map_IU()
RETURNS TRIGGER AS $PROC_other_map_IU$
DECLARE
    id      bigint;
    zone    box;
BEGIN
    SELECT map.id, map.zone INTO id, zone
    FROM map
    WHERE map.id = NEW.id;

    IF zone = NEW.zone THEN
        RAISE EXCEPTION '%\'s zone overlaps with existing %\'s zone', NEW.id, id;
    END IF;

    RETURN NEW;
END;
$PROC_other_map_IU$ LANGUAGE plpgsql;

CREATE TRIGGER TR_other_map_IU
AFTER INSERT OR UPDATE
ON other_map
FOR EACH ROW EXECUTE PROCEDURE PROC_other_map_IU();

Now obviously this is wrong, because it simply checks if the zone attributes are equal.

Thank you in advance for your input! Cheers!

Answer 1

Took me a while, but Postgres' geometric functions and operators (more specifically the && - or overlap - operator) do exactly what I wanted:

IF zone && NEW.zone THEN