Generated Columns in PostgreSQL: GENERATED ALWAYS AS STORED

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

Например:

• итоговая сумма заказа зависит от суммы без налога и налоговой ставки;
• нормализованный email зависит от обычного email;
• полное имя зависит от имени и фамилии;
• дата без времени зависит от created_at;
• скидка или комиссия считается по формуле.

Можно каждый раз считать это в приложении. Можно считать в каждом SELECT. Можно написать триггер. Но во всех этих вариантах есть риск: где-то забыли пересчитать, где-то сделали формулу чуть иначе, где-то руками обновили данные через psql — и значения начали расходиться.

Генерируемый столбец решает эту проблему на уровне самой таблицы.

Он говорит базе:

Это поле не вводится вручную. База сама вычисляет его по формуле.

В PostgreSQL для этого используется конструкция:

GENERATED ALWAYS AS (...) STORED

---

Простая идея: база сама считает значение

Представим таблицу заказов.

У заказа есть сумма без налога:

amount

Есть налоговая ставка:

tax_rate

И есть итоговая сумма:

total = amount + налог

Можно хранить только amount и tax_rate, а итог каждый раз считать в запросе:

SELECT amount, tax_rate, amount * (1 + tax_rate) AS total
FROM orders;

Это рабочий вариант.

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

Где-то напишут:

amount * (1 + tax_rate)

где-то:

amount + amount * tax_rate

где-то забудут округление.

А потом начнутся вопросы:

Почему в админке total один, а в отчёте другой?
Почему в API сумма 1200.00, а в SQL-запросе 1199.999999?
Где вообще считается итог заказа?

Генерируемый столбец позволяет положить формулу в одно место — прямо в схему таблицы.

---

Пример GENERATED ALWAYS AS STORED

Создадим таблицу заказов:

CREATE TABLE orders (
    id         bigint PRIMARY KEY,
    user_id    bigint NOT NULL,
    amount     numeric(12,2) NOT NULL,
    tax_rate   numeric(4,3) NOT NULL DEFAULT 0.200,
    status     text NOT NULL,
    created_at timestamptz NOT NULL DEFAULT now(),

    total numeric(12,2)
        GENERATED ALWAYS AS (round(amount * (1 + tax_rate), 2))
        STORED
);

Здесь total — генерируемый столбец.

Он считается по формуле:

round(amount * (1 + tax_rate), 2)

То есть:

итог = сумма * (1 + налоговая ставка)

Если amount = 50.00, а tax_rate = 0.200, итог будет:

50.00 * 1.200 = 60.00

Вставим заказ:

INSERT INTO orders (id, user_id, amount, status)
VALUES (1, 100, 50.00, 'paid');

Обратите внимание: мы не указываем total.

Теперь посмотрим данные:

SELECT amount, tax_rate, total
FROM orders
WHERE id = 1;

Результат:

amount	tax_rate	total
50.00	0.200	60.00

total появился сам. Его посчитал PostgreSQL.

---

Почему нельзя просто записать значение руками

Генерируемый столбец называется GENERATED ALWAYS не просто так.

ALWAYS означает: значение всегда вычисляет база.

Пользователь, приложение или разработчик не могут вставить туда своё значение.

Например, такой запрос упадёт:

INSERT INTO orders (id, user_id, amount, status, total)
VALUES (2, 101, 10.00, 'paid', 12.00);

PostgreSQL не разрешит вручную записать total, потому что это не обычная колонка.

И это хорошо.

Иначе мы снова получили бы риск расхождения:

amount = 10.00
tax_rate = 0.200
total = 999.00

С генерируемым столбцом такая ситуация невозможна. Если формула говорит, что итог должен быть 12.00, значит он и будет 12.00.

---

Что значит STORED

В PostgreSQL генерируемый столбец может быть хранимым — STORED.

Это значит, что результат формулы физически записывается в таблицу, почти как обычное поле.

Когда вы вставляете строку, PostgreSQL считает значение и сохраняет его.

Когда вы обновляете исходные поля, PostgreSQL пересчитывает значение и снова сохраняет его.

Например:

UPDATE orders
SET amount = 100.00
WHERE id = 1;

После этого total тоже изменится:

SELECT amount, tax_rate, total
FROM orders
WHERE id = 1;

Результат:

amount	tax_rate	total
100.00	0.200	120.00

Мы обновили только amount, но total пересчитался автоматически.

Это и есть главная польза: значение всегда согласовано с исходными данными.

---

Чем generated column отличается от DEFAULT

Начинающие иногда путают генерируемые столбцы и значения по умолчанию.

Например:

created_at timestamptz DEFAULT now()

DEFAULT срабатывает, когда мы вставляем строку и не передаём значение явно.

Но дальше это обычное поле. Его можно обновить руками.

А генерируемый столбец работает иначе:

total numeric(12,2)
    GENERATED ALWAYS AS (round(amount * (1 + tax_rate), 2))
    STORED

Значение total нельзя задать вручную. Оно всегда зависит от формулы.

Главная разница такая:

Механизм	Когда считается	Можно ли перезаписать вручную
DEFAULT	При вставке строки, если значение не передали	Да
GENERATED ALWAYS AS	При вставке и при изменении исходных полей	Нет

DEFAULT отвечает на вопрос:

Что поставить, если значение не передали?

Генерируемый столбец отвечает на другой вопрос:

Как всегда вычислять это значение из других колонок?

---

Один источник правды

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

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

Это значит, что одинаковый результат получат все:

• бэкенд;
• админка;
• аналитический скрипт;
• ETL-процесс;
• разработчик, который руками делает запрос через psql;
• тестировщик, который проверяет данные напрямую в базе.

Никто не должен помнить:

А как именно мы считаем total?
С округлением или без?
С налогом 20% или с tax_rate из строки?
А email мы чистим от пробелов или только приводим к нижнему регистру?

Правило уже записано в таблице.

---

Пример с нормализацией email

Генерируемые столбцы удобны не только для денег.

Частый пример — нормализованный email.

Пользователь может ввести email так:

  Bob@Example.COM

А мы хотим искать его как:

bob@example.com

То есть:

• убрать пробелы по краям;
• привести к нижнему регистру.

Создадим таблицу пользователей:

CREATE TABLE users (
    id         bigint PRIMARY KEY,
    email      text NOT NULL,
    email_norm text
        GENERATED ALWAYS AS (lower(btrim(email)))
        STORED
);

Теперь вставим пользователя:

INSERT INTO users (id, email)
VALUES (1, '  Bob@Example.COM ');

Посмотрим, что получилось:

SELECT email, email_norm
FROM users
WHERE id = 1;

Результат:

email	email_norm
Bob@Example.COM	bob@example.com

Теперь можно искать пользователя по нормализованному значению:

SELECT id, email
FROM users
WHERE email_norm = lower(btrim('  BOB@example.com '));

Нам больше не нужно каждый раз вспоминать, как именно мы нормализуем email. Это правило уже живёт в таблице.

---

Индекс на генерируемом столбце

Так как STORED-столбец хранится физически, его можно индексировать как обычную колонку.

Например, индекс по итоговой сумме заказа:

CREATE INDEX idx_orders_total
ON orders (total);

Теперь запросы вида:

SELECT *
FROM orders
WHERE total > 1000;

могут использовать индекс.

То же самое с нормализованным email:

CREATE INDEX idx_users_email_norm
ON users (email_norm);

После этого поиск по email_norm может быть быстрым:

SELECT id
FROM users
WHERE email_norm = lower(btrim('  Bob@Example.COM '));

Это часто выглядит чище, чем постоянно писать выражение прямо в запросах.

Сравните.

Без генерируемого столбца:

SELECT id
FROM users
WHERE lower(btrim(email)) = lower(btrim('  Bob@Example.COM '));

С генерируемым столбцом:

SELECT id
FROM users
WHERE email_norm = lower(btrim('  Bob@Example.COM '));

Во втором варианте намерение читается проще: мы ищем по нормализованному email.

---

Генерируемый столбец или функциональный индекс?

В PostgreSQL можно решить похожую задачу и через функциональный индекс.

Например:

CREATE INDEX idx_users_email_expr
ON users ((lower(btrim(email))));

Тогда запрос:

SELECT id
FROM users
WHERE lower(btrim(email)) = lower(btrim('  Bob@Example.COM '));

тоже сможет использовать индекс.

Что выбрать: функциональный индекс или генерируемый столбец?

Зависит от задачи.

Функциональный индекс хорош, если выражение нужно только для ускорения поиска, а видеть его как отдельное поле не обязательно.

Генерируемый столбец хорош, если производное значение хочется явно хранить в модели данных, показывать в SELECT, использовать в разных запросах, индексах, ограничениях или просто сделать схему более понятной.

Проще говоря:

Нужно только ускорить WHERE по выражению — можно взять функциональный индекс.
Нужно полноценное понятное поле в таблице — берите generated column.

---

Ограничения: что можно писать в формуле

Генерируемый столбец не может быть любой магией.

PostgreSQL требует, чтобы формула была предсказуемой и зависела только от текущей строки.

Разрешены выражения вроде:

amount * (1 + tax_rate)

lower(btrim(email))

first_name || ' ' || last_name

created_at::date

Но нельзя использовать всё подряд.

Основные правила:

• нельзя обращаться к другим таблицам;
• нельзя писать подзапросы;
• нельзя ссылаться на другие генерируемые столбцы;
• нельзя использовать непредсказуемые функции вроде random() или now();
• функции в выражении должны быть IMMUTABLE, то есть для одинаковых входных данных всегда давать одинаковый результат.

Например, такой столбец создать нельзя:

ALTER TABLE orders
ADD COLUMN created_day_count int
    GENERATED ALWAYS AS (now()::date - created_at::date)
    STORED;

Почему?

Потому что now() меняется со временем.

Сегодня разница между now() и created_at одна, завтра другая. А генерируемый STORED-столбец должен быть результатом стабильной формулы, которую можно пересчитывать при записи строки.

Если вам нужно значение, зависящее от текущего времени, обычно лучше использовать обычный запрос или представление.

Например:

SELECT id,
       created_at,
       now()::date - created_at::date AS age_days
FROM orders;

---

Нельзя ссылаться на другую таблицу

Допустим, мы хотим хранить в заказе имя пользователя:

orders.user_name = users.name

На первый взгляд может показаться, что это тоже хороший кандидат для generated column.

Но так нельзя:

-- This is NOT allowed
user_name text
    GENERATED ALWAYS AS (
        SELECT name FROM users WHERE users.id = user_id
    )
    STORED

Генерируемый столбец работает только с данными той же строки.

Он не умеет ходить в другие таблицы.

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

Если значение зависит от других таблиц, обычно используют:

• JOIN в запросе;
• VIEW;
• материализованное представление;
• триггер;
• отдельную денормализацию на уровне приложения или ETL.

---

Когда лучше использовать VIEW

Если производное значение нужно только при чтении, его не обязательно хранить в таблице.

Например, итоговую сумму можно вынести в представление:

CREATE VIEW order_totals AS
SELECT
    id,
    user_id,
    amount,
    tax_rate,
    round(amount * (1 + tax_rate), 2) AS total
FROM orders;

Теперь можно читать:

SELECT *
FROM order_totals
WHERE total > 1000;

VIEW не хранит total как отдельное значение. Оно считается при чтении.

Это хорошо, когда:

• значение не нужно индексировать как отдельную колонку;
• не хочется занимать место на диске;
• формула нужна только для удобства запросов;
• данные должны всегда считаться «на лету».

Но если значение часто фильтруется, индексируется или используется как часть модели данных, STORED-столбец может быть удобнее.

---

Когда лучше использовать триггер

Триггер нужен, когда логика сложнее, чем простая формула по текущей строке.

Например:

• нужно взять данные из другой таблицы;
• нужно использовать текущее время;
• нужно выполнить условную бизнес-логику;
• нужно вести историю изменений;
• нужно обновлять несколько таблиц;
• нужно разрешить ручное переопределение значения.

Пример идеи для триггера:

При создании заказа записать текущий курс валюты из таблицы exchange_rates.

Это уже не подходит для generated column, потому что формула зависит от другой таблицы.

В таком случае можно использовать BEFORE INSERT или BEFORE UPDATE триггер.

Но у триггеров есть минус: их сложнее читать, тестировать и сопровождать. Логика становится менее очевидной, чем простая формула в описании колонки.

Поэтому правило такое:

Если формула простая и зависит только от текущей строки — generated column.
Если нужна сложная логика или другие таблицы — триггер.

---

GENERATED STORED и миграции на больших таблицах

Есть ещё один практический момент.

Если добавить STORED-столбец в уже большую таблицу, PostgreSQL должен будет записать значения для существующих строк.

Например:

ALTER TABLE orders
ADD COLUMN total numeric(12,2)
    GENERATED ALWAYS AS (round(amount * (1 + tax_rate), 2))
    STORED;

На маленькой таблице всё пройдёт быстро.

На большой таблице это может быть тяжёлой операцией, потому что базе нужно пройти по старым строкам и физически заполнить новый столбец.

Поэтому на проде такие миграции лучше планировать аккуратно:

• проверить на копии базы;
• оценить время выполнения;
• запускать в спокойное окно;
• смотреть блокировки;
• заранее подумать, нужен ли именно STORED, или достаточно VIEW/функционального индекса.

Генерируемый столбец — удобный инструмент, но он не отменяет здравый смысл при изменении больших таблиц.

---

PostgreSQL 18 и VIRTUAL-столбцы

В старых версиях PostgreSQL генерируемые столбцы были только хранимыми, то есть STORED.

Начиная с PostgreSQL 18, появились ещё и виртуальные generated columns.

Разница такая:

Вид	Когда считается	Хранится на диске
STORED	При вставке или обновлении строки	Да
VIRTUAL	При чтении строки	Нет

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

Если вы работаете с PostgreSQL 18 и новее, лучше явно писать STORED, когда вам нужен именно хранимый столбец:

total numeric(12,2)
    GENERATED ALWAYS AS (round(amount * (1 + tax_rate), 2))
    STORED

Так схема читается однозначно.

---

Подвох с lower(), email и collation

Пример с lower(btrim(email)) удобен для обучения, но в реальных проектах с email есть нюансы.

Функция lower() зависит от правил сравнения и преобразования символов в конкретной базе данных. Для обычных латинских email это чаще всего не создаёт проблем, но при смене collation или обновлении правил сортировки/сравнения лучше помнить: уже сохранённые значения в STORED-столбце сами по себе не пересчитаются просто потому, что поменялись внешние правила.

Если вы меняете collation или серьёзно обновляете окружение, такие значения может понадобиться пересоздать.

Для простого старта это не главное, но для продакшена полезно держать в голове: STORED означает «значение уже записано на диск». Если изменились правила, по которым оно раньше считалось, старые строки нужно пересчитать отдельно.

---

Что в MySQL и MariaDB

В MySQL и MariaDB тоже есть генерируемые столбцы.

Там обычно встречаются два вида:

• VIRTUAL — значение считается при чтении;
• STORED — значение считается при записи и хранится на диске.

Пример в MySQL:

CREATE TABLE orders (
    id       bigint PRIMARY KEY,
    amount   decimal(12,2) NOT NULL,
    tax_rate decimal(4,3) NOT NULL DEFAULT 0.200,
    total    decimal(12,2) AS (round(amount * (1 + tax_rate), 2)) STORED
);

Идея похожая: формула живёт в схеме, а не в приложении.

Но детали синтаксиса, ограничения и возможности индексации могут отличаться, поэтому при работе с конкретной СУБД всегда нужно смотреть её документацию.

---

Что в ClickHouse

В ClickHouse есть похожая идея — MATERIALIZED-столбцы.

Пример:

CREATE TABLE orders
(
    id       UInt64,
    amount   Decimal(12, 2),
    tax_rate Decimal(4, 3),
    total    Decimal(12, 2) MATERIALIZED amount * (1 + tax_rate)
)
ENGINE = MergeTree
ORDER BY id;

Такой столбец вычисляется при вставке данных.

Но ClickHouse — это отдельный мир. Он устроен иначе, чем PostgreSQL: другая модель хранения, другие сценарии использования, другой подход к ограничениям и изменениям схемы.

Поэтому не стоит механически переносить правила из PostgreSQL в ClickHouse. Идея похожая — значение считается автоматически, — но детали работы отличаются.

---

Когда использовать генерируемый столбец

Генерируемый STORED-столбец хорошо подходит, если:

• значение всегда считается по одной и той же формуле;
• формула зависит только от колонок этой же строки;
• значение нужно часто читать;
• по нему нужен индекс;
• вы хотите убрать дублирование формулы из приложения;
• важно, чтобы данные не расходились.

Хорошие примеры:

total = amount * (1 + tax_rate)

email_norm = lower(btrim(email))

full_name = first_name || ' ' || last_name

created_date = created_at::date

Плохие примеры:

age_days = now() - created_at

user_name = значение из таблицы users

rating = средняя оценка из таблицы reviews

Плохие — не потому что эти значения не нужны, а потому что они не подходят под ограничения generated column.

---

Короткая шпаргалка

Задача	Что выбрать
Простая формула по полям этой же строки	GENERATED ALWAYS AS (...) STORED
Значение нужно индексировать как отдельную колонку	STORED generated column
Выражение нужно только для ускорения поиска	Функциональный индекс
Значение нужно только при чтении	VIEW
Значение зависит от других таблиц	JOIN, VIEW, триггер или денормализация
Нужны now(), random() или сложная логика	Не generated column, лучше другой механизм
Нужно вручную переопределять значение	Обычная колонка или триггер

---

Главное, что нужно запомнить

Генерируемый столбец — это колонка, которую PostgreSQL считает сам.

Вы описываете формулу один раз:

total numeric(12,2)
    GENERATED ALWAYS AS (round(amount * (1 + tax_rate), 2))
    STORED

А дальше база сама поддерживает значение в актуальном состоянии.

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

Для новичка главное правило такое:

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

А дальше выбирайте инструмент по ситуации:

• нужна простая формула и хранение результата — GENERATED ALWAYS AS (...) STORED;
• нужно только посчитать при чтении — VIEW;
• нужна сложная логика — триггер;
• нужно ускорить поиск по выражению — функциональный индекс.

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