Оператор EXCLUDE в PostgreSQL: Продвинутые ограничения для базы данных

В процессе проектирования базы данных описанном в моей предыдущей статье я решил использовать ограничение EXCLUDE для поддержания корректности вносимых данных. Продолжая размышлять над этим я решил что оператор EXCLUDE достоин описания в отдельной статье.

Введение

PostgreSQL известна множеством мощных функциональных возможностей, и одна из них — оператор EXCLUDE. Этот оператор позволяет создавать продвинутые ограничения на множества значений внутри столбцов таблицы. В этой статье хочу рассказать про оператор EXCLUDE, показать примеры его использования, чтобы вы могли лучше понять, как использовать его для создания гибких и эффективных баз данных.

Оператор EXCLUDE как и UNIQUE в PostgreSQL используется для определения ограничений на множества значений внутри столбцов таблицы. В отличие от UNIQUE он позволяет указать правила, которые определяют, какие значения не могут существовать вместе в определенном столбце или наборе столбцов. Оператор EXCLUDE часто используется с индексом типа GiST или SP-GiST, чтобы обеспечить эффективность выполнения запросов, хотя может применяться и вместе с обычным B-Tree индексом.

Примеры использования

1. Стандартным примером использования EXCLUDE может служить ограничение на пересечение временных интервалов (например сеансов в кинозале).

create table events (
    id serial primary key,
    event_time tstzrange,        -- дата и время начала и конца
    constraint no_screening_time_overlap exclude using gist (
        event_time WITH &&     -- проверка на пересечение
    )
);

insert into events (event_time) values ('["2023-01-01 19:00:00", "2023-01-01 20:45:00"]');

На примере выше код для создания таблицы и внесения одной записи. SQL можно проверить на SQLize.online

После этого Вы можете попробовать вставить еще одну строку с интервалом пересекающим один из уже существующих в таблице. Скорей всего у Вас не получится и база данных вернет ошибку. Если же Вы преуспеете — отпишитесь в комментах!

Так же как и UNIQUE, ограничение EXCLUDE может налагаться на группу столбцов. Например Вы можете использовать колонки event_start и event_end типа timestamp и также ограничить наложения по времени. Ниже пример:

CREATE TABLE events (
    event_id SERIAL PRIMARY KEY,
    event_name VARCHAR(100) NOT NULL,
    event_start TIMESTAMPTZ NOT NULL,
    event_end TIMESTAMPTZ NOT NULL,
    EXCLUDE USING GIST (event_start WITH &&, event_end WITH &&)
);

Аналогично временным диапазонам можно накладывать ограничения на диапазону чисел:

CREATE TABLE ranges (
    range_id SERIAL PRIMARY KEY,
    start_value INTEGER NOT NULL,
    end_value INTEGER NOT NULL,
    EXCLUDE USING GIST (int4range(start_value, end_value, '[]') WITH &&)
);

В этом примере создается таблица ranges, которая содержит числовые диапазоны. Оператор EXCLUDE с индексом GiST указывает, что числовые диапазоны в столбцах start_value и end_value не могут перекрываться.

Другим важным применением может быть ограничение на пересечение геометрических фигур:

CREATE TABLE polygons (
    polygon_id SERIAL PRIMARY KEY,
    polygon_data geometry(Polygon) NOT NULL,
    EXCLUDE USING GIST (polygon_data WITH &&)
);

В этом примере создается таблица polygons, которая содержит информацию о многоугольниках. Оператор EXCLUDE с индексом GiST указывает, что геометрические объекты в столбце polygon_data не могут пересекаться или содержаться друг в друге.

Во всех примерах выше мы использовали ограничение EXCLUDE основанное на GIST индексе. Однако для полноты картины можно привести пример использования R-Tree:

CREATE TABLE users (
    user_id SERIAL PRIMARY KEY,
    email VARCHAR(255) NOT NULL,
    EXCLUDE USING btree (lower(email) WITH =)
);

В этом примере мы практически повторили функционал ограничения UNIQUE с небольшой доработкой: теперь наша уникальность не чувствительна к регистру.

Заключение

Оператор EXCLUDE в PostgreSQL предоставляет возможность создания продвинутых ограничений на множества значений внутри столбцов таблицы. Он позволяет определить правила, которые ограничивают комбинации значений, которые не могут существовать вместе. Это особенно полезно для обеспечения целостности данных и выполнения сложных проверок на уровне базы данных.

В этой статье мы рассмотрели несколько примеров использования оператора EXCLUDE, включая ограничение на пересечение интервалов времени, запрет пересечения геометрических объектов и ограничение на неперекрывающиеся числовые диапазоны. Оператор EXCLUDE является мощным инструментом, который можно использовать для создания гибких и эффективных баз данных в PostgreSQL.

Используйте оператор EXCLUDE в своих проектах, чтобы создавать сложные ограничения и обеспечивать целостность данных на уровне базы данных. Это поможет вам сохранить структурированность и надежность вашей базы данных и обеспечить ее эффективное использование.