Generated columns в PostgreSQL: вычисляемые столбцы

Generated columns — столбцы, значение которых автоматически вычисляется из других столбцов. Добавлены в PostgreSQL 12. Избавляют от триггеров и дублирования логики.

Синтаксис

CREATE TABLE orders (
    id          SERIAL PRIMARY KEY,
    quantity    INTEGER NOT NULL,
    unit_price  NUMERIC(10,2) NOT NULL,
    total_price NUMERIC(10,2) GENERATED ALWAYS AS (quantity * unit_price) STORED
    --                                             ↑ выражение               ↑ обязательно
);

STORED — значение вычисляется при INSERT/UPDATE и физически хранится на диске. PostgreSQL поддерживает только STORED (виртуальные — в будущих версиях).

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

Полное имя

CREATE TABLE users (
    id         SERIAL PRIMARY KEY,
    first_name TEXT NOT NULL,
    last_name  TEXT NOT NULL,
    full_name  TEXT GENERATED ALWAYS AS (first_name || ' ' || last_name) STORED
);

INSERT INTO users (first_name, last_name) VALUES ('Иван', 'Иванов');

SELECT full_name FROM users;
-- Иван Иванов

Геометрия

CREATE TABLE rectangles (
    id     SERIAL PRIMARY KEY,
    width  NUMERIC NOT NULL,
    height NUMERIC NOT NULL,
    area   NUMERIC GENERATED ALWAYS AS (width * height) STORED,
    perimeter NUMERIC GENERATED ALWAYS AS (2 * (width + height)) STORED
);

Возраст из даты рождения

CREATE TABLE persons (
    id          SERIAL PRIMARY KEY,
    birth_date  DATE NOT NULL,
    age_years   INTEGER GENERATED ALWAYS AS (
        EXTRACT(YEAR FROM age(birth_date))::INTEGER
    ) STORED
);

⚠️ age() зависит от CURRENT_DATE — это VOLATILE функция. Фактически PostgreSQL разрешает её в generated columns, но значение вычисляется только при INSERT/UPDATE, не обновляется автоматически каждый день.

Нормализация для поиска

CREATE TABLE products (
    id           SERIAL PRIMARY KEY,
    name         TEXT NOT NULL,
    name_lower   TEXT GENERATED ALWAYS AS (LOWER(name)) STORED,
    name_search  TEXT GENERATED ALWAYS AS (
        regexp_replace(LOWER(name), '[^а-яa-z0-9]', ' ', 'g')
    ) STORED
);

-- Индекс на вычисляемый столбец (удобнее функционального индекса)
CREATE INDEX idx_products_name_lower ON products (name_lower);
CREATE INDEX idx_products_name_search ON products USING gin(to_tsvector('russian', name_search));

-- Поиск без функции в WHERE (индекс работает)
SELECT * FROM products WHERE name_lower = 'ноутбук apple';

Категория по значению

CREATE TABLE transactions (
    id        SERIAL PRIMARY KEY,
    amount    NUMERIC NOT NULL,
    category  TEXT GENERATED ALWAYS AS (
        CASE
            WHEN amount >= 100000 THEN 'large'
            WHEN amount >= 10000  THEN 'medium'
            ELSE 'small'
        END
    ) STORED
);

-- Можно индексировать
CREATE INDEX idx_tx_category ON transactions (category);

-- Фильтрация
SELECT COUNT(*), SUM(amount) FROM transactions
WHERE category = 'large';

Ограничения generated columns

-- ❌ Нельзя указать значение при INSERT/UPDATE
INSERT INTO orders (quantity, unit_price, total_price) VALUES (5, 100, 500);
-- ERROR: cannot insert into column "total_price"

-- ✅ Указываем только исходные столбцы
INSERT INTO orders (quantity, unit_price) VALUES (5, 100);

-- ❌ Нельзя ссылаться на другой generated column
CREATE TABLE t (
    a INTEGER,
    b INTEGER GENERATED ALWAYS AS (a * 2) STORED,
    c INTEGER GENERATED ALWAYS AS (b * 3) STORED  -- ❌ ссылка на b
);

-- ❌ Нельзя использовать подзапросы
CREATE TABLE t (
    user_id INTEGER,
    total   NUMERIC GENERATED ALWAYS AS (
        (SELECT SUM(amount) FROM orders WHERE user_id = t.user_id)  -- ❌
    ) STORED
);

-- ❌ Нельзя использовать VOLATILE функции (NOW(), RANDOM())
-- Но STABLE функции (age(), LOWER()) — можно

Generated vs функциональный индекс

-- Функциональный индекс — только для запросов
CREATE INDEX idx_email_lower ON users (LOWER(email));
-- Запрос должен использовать ту же функцию: WHERE LOWER(email) = ...

-- Generated column — значение всегда доступно
ALTER TABLE users ADD COLUMN email_lower TEXT
    GENERATED ALWAYS AS (LOWER(email)) STORED;
CREATE INDEX idx_email_lower ON users (email_lower);
-- Запрос: WHERE email_lower = ...  (проще, без функции)
-- Или: SELECT email_lower FROM users (не нужно писать LOWER каждый раз)

Когда generated лучше:

• Используете вычисленное значение в нескольких местах (SELECT, WHERE, JOIN)
• Хотите явно видеть колонку в схеме
• Нужен индекс + SELECT на то же значение

Когда функциональный индекс лучше:

• Не хотите менять схему таблицы
• Нужно только ускорить конкретный запрос

Generated vs триггер

Триггер для вычисления:

-- Старый способ: триггер BEFORE INSERT/UPDATE
CREATE OR REPLACE FUNCTION calc_total()
RETURNS TRIGGER AS $$
BEGIN
    NEW.total_price := NEW.quantity * NEW.unit_price;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_calc_total
BEFORE INSERT OR UPDATE ON orders
FOR EACH ROW EXECUTE FUNCTION calc_total();

Generated column:

-- Новый способ: одна строка в DDL
total_price NUMERIC GENERATED ALWAYS AS (quantity * unit_price) STORED

Generated columns проще: нет лишнего кода, нет отдельного триггера, логика видна прямо в схеме.

Когда триггер всё равно нужен:

• Вычисление зависит от данных в других таблицах (подзапросы)
• Нужна условная логика с side effects
• Обновление зависит от предыдущего значения строки

Добавить к существующей таблице

-- Добавить generated column к существующей таблице
ALTER TABLE orders
ADD COLUMN total_price NUMERIC
GENERATED ALWAYS AS (quantity * unit_price) STORED;

-- PostgreSQL сразу вычислит значения для всех существующих строк
-- На большой таблице это может занять время и заблокировать таблицу

Просмотр generated columns

-- Список generated columns в таблице
SELECT
    column_name,
    data_type,
    generation_expression,
    is_generated
FROM information_schema.columns
WHERE table_name = 'orders'
  AND is_generated = 'ALWAYS';

Итог

Generated columns — простое и элегантное решение для вычислений, которые зависят только от текущей строки. Для остального — триггеры.