Оператор EXCLUDE в PostgreSQL: Просунуті обмеження для бази даних

PostgreSQL відома своїм багатством потужних функціональних можливостей, і одна з них - оператор EXCLUDE. Цей оператор дозволяє створювати продвинуті обмеження для множини значень всередині стовпців таблиці. У цій статті я хочу розповісти про оператор EXCLUDE, показати приклади його використання, щоб ви могли краще розуміти, як використовувати його для створення гнучких та ефективних баз даних.

Оператор EXCLUDE, подібно до UNIQUE використовується для встановлення обмежень на множини значень всередині стовпців таблиці. У відміну від 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"]');

Після цього ви можете спробувати вставити ще один рядок з інтервалом, який перетинає один з уже існуючих у таблиці. Ймовірно, вам не вдасться, і база даних поверне помилку. Якщо ж ви успішно це зробите, будь ласка, повідомте у коментарях!

Так само, як і 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 не можуть перетинатися або міститися один в одному.

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

У цьому прикладі ми майже повторили функціонал обмеження UNIQUE з невеликою модифікацією: тепер наша унікальність не залежить від регістру.

Висновок

Оператор EXCLUDE в PostgreSQL надає можливість створення складних обмежень на множини значень у стовпцях таблиці. Він дозволяє визначити правила, які обмежують комбінації значень, які не можуть існувати разом. Це особливо корисно для забезпечення цілісності даних та виконання складних перевірок на рівні бази даних.

У цій статті ми розглянули кілька прикладів використання оператора EXCLUDE, включаючи обмеження на перетин інтервалів часу, заборону перетину геометричних об'єктів і обмеження на неперекриваючіся числові діапазони. Оператор EXCLUDE є потужним інструмент