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

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 в так нельзя.

ВАЖНО!!!

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

 

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

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

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

SELECT * FROM test.rrr LIMIT 100;

Результат:

Таблица с вычисляемым столбцом в PostgreSQL
Таблица с вычисляемым столбцом в 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
Таблица без вычисляемого столбца в 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
Получили доменные имена глубиной в 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