Un valor derivado — un impuesto, el total de un pedido, un email normalizado — suele duplicarse por toda la aplicacion y diverger con el tiempo. Una columna generada lo calcula dentro de la tabla, de modo que el valor permanece coherente con sus datos de origen y vive en un unico sitio.
Que hace GENERATED ALWAYS AS
Una columna generada es una columna cuyo valor calcula la base de datos a partir de otras columnas de la misma fila, mediante una expresion fija. En PostgreSQL siempre es STORED: el resultado se escribe fisicamente en disco como cualquier columna y se recalcula cada vez que se insertan o actualizan los campos de origen.
CREATE TABLE orders (
id bigint PRIMARY KEY,
user_id bigint NOT NULL,
amount numeric(12,2) NOT NULL,
status text NOT NULL,
created_at timestamptz NOT NULL DEFAULT now(),
tax_rate numeric(4,3) NOT NULL DEFAULT 0.20,
total numeric(12,2) GENERATED ALWAYS AS (amount * (1 + tax_rate)) STORED
);
INSERT INTO orders (id, user_id, amount, status)
VALUES (1, 100, 50.00, 'paid');
SELECT amount, tax_rate, total FROM orders WHERE id = 1;
No puedes escribir en total de forma directa: es tarea del servidor.
INSERT INTO orders (id, user_id, amount, status, total)
VALUES (2, 101, 10.00, 'paid', 12.00);
Una unica fuente de verdad
La verdadera ventaja es un invariante en el nivel del esquema. Todos los clientes — el backend, un proceso ETL, una sesion suelta de psql — ven el mismo valor, y nadie puede olvidarse de recalcularlo. Resulta comodo para normalizar:
ALTER TABLE users
ADD COLUMN email_norm text
GENERATED ALWAYS AS (lower(btrim(email))) STORED;
SELECT id FROM users WHERE email_norm = lower(btrim(' Bob@Example.COM '));
Ahora las busquedas por email ignoran mayusculas y espacios, y ya no normalizas la columna en cada consulta.
Indexar una columna generada
Como el valor es STORED, puedes indexarlo como cualquier otra columna; suele ser mas limpio que un indice de expresion sobre la formula original.
CREATE INDEX idx_orders_total ON orders (total);
CREATE INDEX idx_users_email_norm ON users (email_norm);
EXPLAIN SELECT * FROM orders WHERE total > 1000;
Una consulta de rango sobre total usa el indice directamente, sin volver a evaluar amount * (1 + tax_rate).
Restricciones a tener presentes
La expresion debe ser determinista y depender solo de la fila actual:
- Solo se permiten funciones
IMMUTABLE. now(), random(), las referencias a otras tablas y las subconsultas se rechazan.
- Puede referenciar unicamente columnas de la misma fila, y no otras columnas generadas.
- PostgreSQL solo admite
STORED; no hay columnas generadas VIRTUAL antes de la version 18.
- No puedes asignar el valor a mano, solo mediante
DEFAULT.
ALTER TABLE orders
ADD COLUMN age_days int
GENERATED ALWAYS AS (now() - created_at) STORED;
Trampa: lower() esta marcada como IMMUTABLE, pero su resultado depende del LC_COLLATE de la base. Si luego cambias la collation, los valores almacenados en email_norm habra que reconstruirlos a mano: el servidor no los recalculara por ti.
Cuando conviene un trigger o una vista
Una columna generada brilla con una formula pura y local a la fila. Mas alla de eso:
- Un trigger
BEFORE INSERT/UPDATE — cuando necesitas datos de otras tablas, valores no deterministas (now()) o logica compleja. El precio es mantener el invariante tu mismo.
- Una
VIEW — cuando el valor derivado solo hace falta al leer y no debe ocupar espacio ni entrar en un indice. Se calcula al vuelo y siempre esta fresco.
CREATE VIEW order_totals AS
SELECT id, amount, amount * (1 + tax_rate) AS total
FROM orders;
Regla practica: formula determinista de una sola fila, usa una columna generada; datos externos o escritura manual, un trigger; solo lectura sin almacenamiento, una vista.
Diferencias en otros motores
- MySQL/MariaDB: admiten tanto
STORED como VIRTUAL (por defecto VIRTUAL). La sintaxis es total AS (amount * 1.2) STORED; ambos tipos se pueden indexar.
- ClickHouse: el equivalente son las columnas
MATERIALIZED; no aparecen en SELECT * y solo se calculan al insertar.
En PostgreSQL quedate con STORED y expresiones IMMUTABLE, y el valor derivado seguira siendo coherente sin una sola linea de codigo en la aplicacion.
Un valor derivado — un impuesto, el total de un pedido, un email normalizado — suele duplicarse por toda la aplicacion y diverger con el tiempo. Una columna generada lo calcula dentro de la tabla, de modo que el valor permanece coherente con sus datos de origen y vive en un unico sitio.
Que hace GENERATED ALWAYS AS
Una columna generada es una columna cuyo valor calcula la base de datos a partir de otras columnas de la misma fila, mediante una expresion fija. En PostgreSQL siempre es
STORED: el resultado se escribe fisicamente en disco como cualquier columna y se recalcula cada vez que se insertan o actualizan los campos de origen.CREATE TABLE orders ( id bigint PRIMARY KEY, user_id bigint NOT NULL, amount numeric(12,2) NOT NULL, status text NOT NULL, created_at timestamptz NOT NULL DEFAULT now(), tax_rate numeric(4,3) NOT NULL DEFAULT 0.20, total numeric(12,2) GENERATED ALWAYS AS (amount * (1 + tax_rate)) STORED ); INSERT INTO orders (id, user_id, amount, status) VALUES (1, 100, 50.00, 'paid'); SELECT amount, tax_rate, total FROM orders WHERE id = 1; -- 50.00 | 0.200 | 60.00No puedes escribir en
totalde forma directa: es tarea del servidor.INSERT INTO orders (id, user_id, amount, status, total) VALUES (2, 101, 10.00, 'paid', 12.00); -- ERROR: cannot insert a non-DEFAULT value into column "total"Una unica fuente de verdad
La verdadera ventaja es un invariante en el nivel del esquema. Todos los clientes — el backend, un proceso ETL, una sesion suelta de
psql— ven el mismo valor, y nadie puede olvidarse de recalcularlo. Resulta comodo para normalizar:ALTER TABLE users ADD COLUMN email_norm text GENERATED ALWAYS AS (lower(btrim(email))) STORED; SELECT id FROM users WHERE email_norm = lower(btrim(' Bob@Example.COM '));Ahora las busquedas por email ignoran mayusculas y espacios, y ya no normalizas la columna en cada consulta.
Indexar una columna generada
Como el valor es
STORED, puedes indexarlo como cualquier otra columna; suele ser mas limpio que un indice de expresion sobre la formula original.CREATE INDEX idx_orders_total ON orders (total); CREATE INDEX idx_users_email_norm ON users (email_norm); EXPLAIN SELECT * FROM orders WHERE total > 1000;Una consulta de rango sobre
totalusa el indice directamente, sin volver a evaluaramount * (1 + tax_rate).Restricciones a tener presentes
La expresion debe ser determinista y depender solo de la fila actual:
IMMUTABLE.now(),random(), las referencias a otras tablas y las subconsultas se rechazan.STORED; no hay columnas generadasVIRTUALantes de la version 18.DEFAULT.-- Falla: now() no es IMMUTABLE ALTER TABLE orders ADD COLUMN age_days int GENERATED ALWAYS AS (now() - created_at) STORED; -- ERROR: generation expression is not immutableTrampa:
lower()esta marcada comoIMMUTABLE, pero su resultado depende delLC_COLLATEde la base. Si luego cambias la collation, los valores almacenados enemail_normhabra que reconstruirlos a mano: el servidor no los recalculara por ti.Cuando conviene un trigger o una vista
Una columna generada brilla con una formula pura y local a la fila. Mas alla de eso:
BEFORE INSERT/UPDATE— cuando necesitas datos de otras tablas, valores no deterministas (now()) o logica compleja. El precio es mantener el invariante tu mismo.VIEW— cuando el valor derivado solo hace falta al leer y no debe ocupar espacio ni entrar en un indice. Se calcula al vuelo y siempre esta fresco.CREATE VIEW order_totals AS SELECT id, amount, amount * (1 + tax_rate) AS total FROM orders;Regla practica: formula determinista de una sola fila, usa una columna generada; datos externos o escritura manual, un trigger; solo lectura sin almacenamiento, una vista.
Diferencias en otros motores
STOREDcomoVIRTUAL(por defectoVIRTUAL). La sintaxis estotal AS (amount * 1.2) STORED; ambos tipos se pueden indexar.MATERIALIZED; no aparecen enSELECT *y solo se calculan al insertar.En PostgreSQL quedate con
STOREDy expresionesIMMUTABLE, y el valor derivado seguira siendo coherente sin una sola linea de codigo en la aplicacion.