Сгенерированные столбцы в PostgreSQL

Thursday, November 14, 2019 4:13:42 PM

Введение

Сгенерированный столбец или вычисляемый столбец для столбцов - это то же, что представление для таблицы. PostgreSQL использует термин «Сгенерированные столбцы» ('Generated') для вычисляемых столбцов. Значение столбца всегда вычисляется или генерируется из других столбцов таблицы. Сгенерированный столбец может быть виртуальным или сохраненным. Значения для виртуальных столбцов вычисляются сходу во время запроса, и они не занимают место для хранения. Значения для сохраненных столбцов предварительно вычисляются и сохраняются как часть данных таблицы.

Предпосылки

Современные базы данных, такие как SQL Server и Oracle, уже давно имеют вычисляемые столбцы, а отсутствие вычисляемых столбцов в PostgreSQL затрудняет миграцию из других баз данных. В этой статье предпринята попытка изучить различные способы достижения одинаковой функциональности в разных версиях PostgreSQL.

PostgreSQL 12

PostgreSQL 12 - это следующий крупный выпуск самой популярной в мире базы данных с открытым исходным кодом. Стабильная версия PostgreSQL 12 планируется выйти в конце 2019 года и поддерживает генерируемые столбцы.

Условие GENERATED ALWAYS AS используется для создания сгенерированных столбцов. Выражение, используемое для определения сгенерированного столбца, называется выражением генерации.

-- PostgreSQL syntax
CREATE TABLE employee (
    ...,
    dob timestamp,
    age integer GENERATED ALWAYS AS _
                (date_part('year', CURRENT_TIME) - date_part('year', dob)) STORED
);

Сравнение с вычисляемым столбцом SQL Server

Сравните это с синтаксисом SQL Server. В SQL Server нет специального ключевого слова для объявления вычисляемого столбца. Вы просто указываете выражение, которое делает нас вычисляемым столбцом после предложения AS.

-- SQL Server Syntax
CREATE TABLE Employee (
    ...,
    dob datetime,
    age AS DATEDIFF(year,dob,GETDATE()) PERSISTED
);

Обратите внимание на условие PERSISTED, которое эквивалентно условию PostgreSQL STORED. SQL Server также поддерживает непостоянные вычисляемые столбцы, вы просто не указываете условие PERSISTED. Но в настоящее время PostgreSQL реализует только хранимые сгенерированные столбцы.

Так чем же сгенерированный столбец отличается от обычного столбца с условием DEFAULT?

Столбец по умолчанию оценивается один раз, когда строка впервые вставляется, если не было предоставлено никакого другого значения; сгенерированный столбец обновляется при каждом изменении строки и не может быть переопределен.
Столбцу с ограничением DEFAULT может быть присвоено значение в инструкции INSERT или UPDATE. Сгенерированным столбцам не могут быть заданы значения, они всегда вычисляются.
Столбец по умолчанию не может ссылаться на другие столбцы таблицы, тогда как сгенерированные столбцы специально предназначены для этого.
Столбец по умолчанию может использовать изменяемые функции, например, random () или current_time, сгенерированные столбцы не могут.

Ограничения

Несколько ограничений применяются к определению сгенерированных столбцов и таблиц, включающих сгенерированные столбцы (см. Posgtres Doc2):

Выражение генерации может использовать только неизменяемые функции, а не изменчивые функции. В терминологии SQL Server они называются детерминированными и недетерминированными функциями соответственно.
Выражение генерации никоим образом не может использовать подзапросы или ссылаться на что-либо, кроме текущей строки.
Выражение генерации не может ссылаться на другой сгенерированный столбец.
Выражение генерации не может ссылаться на системный столбец, кроме tableoid.
Сгенерированный столбец не может иметь столбец по умолчанию или определение идентификатора.
Сгенерированный столбец не может быть частью ключа раздела.

Дополнительные предупреждения

Внешние таблицы могут иметь сгенерированные столбцы.
Права доступа для сгенерированных столбцов поддерживаются отдельно от базовых столбцов. Таким образом, вы можете предоставить доступ к ролям для чтения из сгенерированного столбца, но не из базовых базовых столбцов.
Сгенерированные столбцы концептуально обновляются после запуска триггеров. Поэтому изменения, внесенные в базовые столбцы в триггере BEFORE, будут отражены в сгенерированных столбцах. Но сами сгенерированные столбцы не могут быть доступны в триггерах BEFORE.

PostgreSQL 11.x и старше

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

Используйте View

При таком подходе в таблице нет столбца age. Представление используется везде, где необходим столбец age.

CREATE VIEW v_employee AS
SELECT dob, date_part('year', CURRENT_TIME) - date_part('year', dob) as age
FROM employee;

Используйте обычный столбец и обновите его с помощью триггера

В этом подходе «age» объявляется как обычный целочисленный столбец, а триггер заполняет столбец во время вставок и обновлений.

CREATE TABLE employee (     
    ...,     
    dob timestamp,     
    age integer 
);

CREATE OR REPLACE FUNCTION calc_age() RETURNS TRIGGER AS $body$
BEGIN
  NEW.age := date_part('year', CURRENT_TIME) - date_part('year', dob);
  RETURN NEW;
END;
$body$ LANGUAGE plpgsql;

Основным недостатком этого подхода являются дополнительные накладные расходы по обслуживанию, связанные с созданием и поддержанием представлений/триггеров и возможностью их обновления при изменении бизнес-логики. Обновление баз данных до PostgreSQL 12 после выпуска стабильной версии было бы хорошей идеей для устранения недостатков и удалите накладные расходы на техническое обслуживание.

SPBDEV Blog