Работа с вычисляемыми колонками в PostgreSQL: гайд

Быстрый ответ

В PostgreSQL присутствует возможность создания вычисляемых столбцов посредством конструкции GENERATED AS. Такие столбцы позволяют проводить вычисления на основе данных других колонок в режиме реального времени. Например, столбец age_years можно создать таким образом:

CREATE TABLE employees (
  name TEXT,
  birthdate DATE,
  age_years INT GENERATED ALWAYS AS (EXTRACT(YEAR FROM AGE(birthdate))) STORED
);

В этом случае значение age_years автоматически рассчитывается с учетом данных из колонки birthdate, что гарантирует актуальность данных без необходимости ручного обновления.

Сопоставление с представлениями и функциями

Дополнительные механизмы, такие как представления и функции, позволяют динамически расширить возможности по работе с данными. Добавим столбец tenure, используя представление:

CREATE VIEW employees_with_tenure AS
SELECT *, EXTRACT(YEAR FROM AGE(hire_date)) AS tenure FROM employees;

Мы также можем определить функцию для имитации вычисляемого столбца:

CREATE FUNCTION tenure(employee employees) RETURNS INT AS $$
BEGIN
  RETURN EXTRACT(YEAR FROM AGE(employee.hire_date));
END;
$$ LANGUAGE plpgsql;

Вызывается она так, как если бы это было поле таблицы:

SELECT name, birthdate, employees.tenure(employees) FROM employees;

Улучшение производительности с помощью индексов выражений

Индексы выражений ускоряют работу с запросами, содержащими вычисляемые выражения:

CREATE INDEX idx_employee_age ON employees ((EXTRACT(YEAR FROM AGE(birthdate))));

Это увеличивает скорость сортировки и поиска по age_years.

Виртуальные столбцы в старых версиях PostgreSQL

В версиях PostgreSQL, предшествующих 12-й, для имитации вычисляемых столбцов можно использовать триггеры:

CREATE OR REPLACE FUNCTION compute_age()
RETURNS TRIGGER AS $$
BEGIN
  NEW.age_years := EXTRACT(YEAR FROM AGE(NEW.birthdate));
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER age_trigger
BEFORE INSERT OR UPDATE ON employees
FOR EACH ROW EXECUTE PROCEDURE compute_age();

В этих версиях материализованные представления выполняют роль кэша, храня результаты сложных вычислений.

Использование правил для обеспечения согласованности

Правила обеспечивают актуальность и последовательность вычисляемых значений:

CREATE OR REPLACE RULE compute_insert AS
ON INSERT TO employees_with_tenure
DO INSTEAD INSERT INTO employees (name, birthdate) VALUES (NEW.name, NEW.birthdate);

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

В контексте ETL-процесса (Extract, Transform, Load), PostgreSQL позволяет эффективно управлять вычисляемыми столбцами:

UPDATE employees e SET age_years = EXTRACT(YEAR FROM AGE(e.birthdate));

Инструменты типа dbfiddle отлично подходят для тестирования и изучения работы с данными.

Поддержка актуальности данных

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

Визуализация

Столбцы в SQL можно воспринимать как ингредиенты в кулинарии:

Обычный столбец (🥔): это как очищенный и нарезанный картофель, готовый к употреблению. Сгенерированный столбец (🧂): это соль, добавляемая в блюдо в процессе приготовления и получаемая из других ингредиентов.

ALTER TABLE meals ADD COLUMN flavor_enhancer GENERATED ALWAYS AS (spice_level * herb_ratio) STORED;

С применением такого столбца в таблице meals, острота блюда всегда будет оптимальной!

Подготовка к будущим улучшениям

Следите за обновлениями: в будущих релизах PostgreSQL могут появиться VIRTUAL сгенерированные столбцы, что сделает работу с данными еще более удобной и гибкой.

Важность документации

Богатые возможности и полнота документации PostgreSQL служат надежным основанием для понимания и использования вычисляемых столбцов.

Нахождение баланса при оптимизации

Важно сохранять баланс между удобством использования, затратами на хранение данных и производительностью при проектировании оптимизированных баз данных.

Полезные материалы

1. PostgreSQL: Documentation: 16: 5.3. Generated Columns — официальная документация PostgreSQL по теме вычисляемых столбцов.
2. Computed column – PostgreSQL wiki — введение в концепцию вычисляемых столбцов в PostgreSQL.
3. sql – Create unique constraint with null columns – Stack Overflow — детальный разбор уникальных ограничений, связанных с вычисляемыми столбцами в PostgreSQL.
4. Practical Use Cases for PostgreSQL Generated Columns — практические примеры использования вычисляемых столбцов.
5. Blog Post on Performance with Generated Columns — анализ производительности вычисляемых столбцов в PostgreSQL.