PostgreSQL generated columns: вычисляемое поле

Категория: Postgresql · Язык: SQL · Обновлено:

GENERATED ALWAYS AS (...) STORED создаёт колонку, значение которой PostgreSQL вычисляет при вставке и обновлении строки и хранит на диске. Она всегда согласована с исходными полями, её можно индексировать и использовать в запросах, но нельзя записать вручную. Это заменяет триггеры для простых производных значений.

Хранить сумму с налогом

SQL
CREATE TABLE invoices (
    id integer PRIMARY KEY,
    amount numeric(10, 2) NOT NULL,
    tax_rate numeric(4, 3) NOT NULL DEFAULT 0.200,
    total numeric(12, 2)
        GENERATED ALWAYS AS (amount * (1 + tax_rate)) STORED
);

INSERT INTO invoices (id, amount) VALUES (1, 1000.00);

SELECT id, amount, tax_rate, total FROM invoices;

total пересчитывается при изменении amount или tax_rate. Попытка вставить или обновить total вручную завершится ошибкой cannot insert a non-DEFAULT value into column "total". Если значение нужно задать явно, используйте DEFAULT или не указывайте колонку в INSERT.

Выражение должно быть неизменяемым (IMMUTABLE): нельзя использовать now(), random(), обращения к другим таблицам и подзапросы. PostgreSQL проверяет это при создании колонки. Для изменяемых вычислений оставьте обычную колонку и обновляйте её триггером или приложением.

STORED появился в PostgreSQL 12. Добавление такой колонки в существующую таблицу переписывает её и блокирует запись на время операции; планируйте миграцию. Для больших таблиц используйте отдельную колонку с backfill или ALTER TABLE ... ADD COLUMN ... GENERATED в окно обслуживания.

Генерируемая колонка занимает место на диске, но экономит логику приложения и гарантирует инвариант. Индексируйте её, если по ней фильтруете или сортируете. Проверить определение можно через information_schema.columns.is_generated или pg_attribute.attgenerated.

Документация: generated columns, CREATE TABLE.