PostgreSQL | Вычисляемый столбец на основании значения другого столбца таблицы

Как создать столбец в таблице, который вычисляется из других столбцов по формуле или условию?

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

Также может быть удобно прописывать различные условия. Основная идея заключается в предоставлении вычисленного значения для удобства формирования запросов и эффективного отбора результатов.

В конце публикации будет рассмотрен реальный пример, где это удобно использовать.

Пример в момент СОЗДАНИЯ таблицы

Мы создаём таблицу командой CREATE TABLE и хотим сразу прописать нужную формулу вычисления значения в ячейках динамического столбца.

CREATE TABLE IF NOT EXISTS test.rrr
  (
    id bigint,
    ff bigint GENERATED ALWAYS AS (id + 10) STORED
  )

В этом примере мы говорим столбцу ff высчитывать своё значение на основании значения в столбце id.

В ff мы просто прибавляем 10 к тому что в id.

За эту логику отвечает конструкция:

GENERATED ALWAYS AS (id + 10) STORED

Эта конструкция называется «ограничением столбца«. Само слово GENERATED означает то, что значение в ячейках столбца — ГЕНЕРИРУЕМОЕ, и поэтому его нельзя записать вручную или изменить напрямую. Его можно только читать.

Ключевое слово ALWAYS обозначает, что при ВСТАВКЕ ЗАПИСИ В ТАБЛИЦУ или при ОБНОВЛЕНИИ ЗАПИСИ В ТАБЛИЦЕ значение ячейки ВСЕГДА будет пересчитываться на основании «генерирующего выражения«.

Ключевое слово AS указывает на отношение. На то, какому столбцу относится «генерируемое выражение«.

Само выражение для генерации — (id + 10). Должно быть заключено в круглые скобки. Указывает на столбец и операцию, которую нужно выполнить. У нас простой пример, поэтому в реальном проекте значение генерации может вычислять куда более сложнее.

Ключевое слово STORED указывает на то, что этот столбец будет вычисляться при записи и сохраняться на диске. Если не писать STORED, то выполнение команды завершится ошибкой. Хотя было бы неплохо иметь возможность при чтении данных из этой ячейки высчитывать значения «на лету», но PostgreSQL в так нельзя.

ВАЖНО!!!

Можно ссылаться только на столбцы ЭТОЙ таблицы, а на другие нельзя. (но это не мешает написать свою собственную триггерную функцию, которая будет ходить в другие таблицы и другие столбцы, а потом дописать к ней подходящий триггер)
Генерируемый столбец может обращаться только к нормальным столбцам с готовыми значениями. Генерируемый столбец НЕ МОЖЕТ использовать в расчётах другие генерируемые столбцы.

Делаем вставку данных:

INSERT INTO test.rrr (id) SELECT unnest(ARRAY[1, 3, 5]);

Смотрим на итоговую таблицу:

SELECT * FROM test.rrr LIMIT 100;

Результат:

Таблица с вычисляемым столбцом в PostgreSQL

Пример в момент ОБНОВЛЕНИЯ таблицы

У нас уже есть таблица и мы хотим добавить в неё новый столбец, который самостоятельно умеет вычислять свои значения по какому-то алгоритму.

CREATE TABLE IF NOT EXISTS test.rrr
  (
    id bigint
  );

INSERT INTO test.rrr (id) SELECT unnest(ARRAY[2, 4, 6]);

SELECT * FROM test.rrr LIMIT 100;

Результат:

Таблица без вычисляемого столбца в PostgreSQL

Добавляем генерируемый столбец через команду изменения таблицы — ALTER TABLE:

ALTER TABLE IF EXISTS test.rrr
ADD COLUMN IF NOT EXISTS ff bigint GENERATED ALWAYS AS (id + 10) STORED;

Проверяем что получилось:

Изменили таблицу - добавили вычисляемый столбец в PostgreSQL — Изменили таблицу — добавили вычисляемый столбец в PostgreSQL

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

Предположим, что у нас есть таблица с именами доменов. Нам необходимо подсчитывать глубину вложенности имени, а также слова, которые можно встретить на каждой глубине.

Воспользовавшись знаниями выше мы можем создать таблицу:

-- Создаём таблицу с ограничениями столбцов
CREATE TABLE IF NOT EXISTS test.sites
  (
    sitename text,
    arrnames text[] GENERATED ALWAYS AS (string_to_array(sitename, '.')) STORED,
    dnsdepth bigint GENERATED ALWAYS AS (array_length(string_to_array(sitename, '.'), 1)) STORED
  );

-- Вставляем тестовые данные
INSERT INTO test.sites (sitename) SELECT unnest(ARRAY['efim360.ru', 'www.efim360.ru', 'nano.mega.postgres.efim360.ru']);

-- Просматриваем результат
SELECT * FROM test.sites LIMIT 100;

Результат:

Таблица с двумя генерируемыми столбцами на основе одного для подсчёта глубины доменного имени - PostgreSQL — Таблица с двумя генерируемыми столбцами на основе одного для подсчёта глубины доменного имени — PostgreSQL

С такой таблицей очень удобно работать. Она сама высчитывает глубину доменного имени на основе оригинального строкового типа.

Это значит, что если у нас будет задача отобрать все доменные имена глубиной 3, то мы сможем мгновенно это сделать:

-- Получаем домены глубиной 3
SELECT * FROM test.sites WHERE dnsdepth = 3 LIMIT 100;

Результат:

Получили доменные имена глубиной в 3 уровня на основе генерируемого столбца в PostgreSQL

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

Информационные ссылки

Официальный сайт WEB-оболочки pgAdmin — https://www.pgadmin.org

Официальный сайт СУБД PostgreSQL — https://www.postgresql.org

Команда создания таблицы CREATE TABLE — https://postgrespro.ru/docs/postgresql/14/sql-createtable

Команда изменения таблицы ALTER TABLE — https://postgrespro.ru/docs/postgresql/14/sql-altertable

Раздел «4.2.12. Конструкторы массивов» — https://postgrespro.ru/docs/postgresql/14/sql-expressions#SQL-SYNTAX-ARRAY-CONSTRUCTORS

Раздел «9.4. Строковые функции и операторы» — https://postgrespro.ru/docs/postgresql/14/functions-string

Раздел «9.19. Функции и операторы для работы с массивами» — https://postgrespro.ru/docs/postgresql/14/functions-array