Вычисляемый столбец PostgreSQL 12: сумма строки заказа

Количество и цена изменились, а сумма строки заказа осталась прежней. Если её пересчитывают несколько клиентов, такой пропуск рано или поздно случится. PostgreSQL 12 умеет вычислять и сохранять производное значение при записи. Зададим формулу в таблице и проверим пересчёт после изменения исходных полей.

Формула принадлежит таблице

CREATE TABLE demo_lines (
    id integer PRIMARY KEY,
    qty integer NOT NULL CHECK (qty > 0),
    unit_price numeric(12,2) NOT NULL CHECK (unit_price >= 0),
    total numeric(16,2) GENERATED ALWAYS AS (qty * unit_price) STORED
);
INSERT INTO demo_lines (id, qty, unit_price) VALUES (1, 3, 19.90);
SELECT id, qty, unit_price, total FROM demo_lines;
UPDATE demo_lines SET qty = 4 WHERE id = 1;
SELECT total FROM demo_lines WHERE id = 1;

После вставки получится 59.70, а после изменения количества — 79.60. На этих значениях удобно проверить формулу. Приложение передаёт исходные поля и читает результат обычным SELECT либо RETURNING, не поддерживая отдельную копию расчётной логики в каждом обработчике.

Что можно включить в выражение

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

Как сохранить цену на момент заказа

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

Измените по очереди исходные поля, попробуйте отрицательное количество и прочитайте итог через драйвер приложения. Произвольную запись в total сервер должен отклонить. Если нужна другая формула, подготовьте миграцию с учётом старых данных и стоимости изменения таблицы: вычисляемый столбец тоже занимает место и требует работы при записи.

Источники и дальнейшее чтение

• PostgreSQL 12: generated columns
• PostgreSQL 12: CREATE TABLE