A derived value — tax, an order total, a normalized email — tends to get duplicated across an application and drift over time. A generated column computes it inside the table, so the value stays consistent with its source data and lives in exactly one place.
What GENERATED ALWAYS AS does
A generated column is a column whose value the database computes from other columns of the same row, using a fixed expression. In PostgreSQL it is always STORED: the result is physically written to disk like an ordinary column and recomputed whenever the source fields are inserted or updated.
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;
You cannot write to total directly — that is the server's job:
INSERT INTO orders (id, user_id, amount, status, total)
VALUES (2, 101, 10.00, 'paid', 12.00);
A single source of truth
The real win is an invariant at the schema level. Every client — backend, ETL job, an ad-hoc psql session — sees the same value, and nobody can forget to recompute it. This is handy for normalization:
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 '));
Email lookups are now case- and whitespace-insensitive, and you no longer normalize the column in every query.
Indexing a generated column
Because the value is STORED, you can index it like any other column — often cleaner than an expression index on the raw formula.
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;
A range query on total uses the index directly, with no re-evaluation of amount * (1 + tax_rate).
Restrictions to keep in mind
The expression must be deterministic and depend only on the current row:
- Only
IMMUTABLE functions are allowed. now(), random(), references to other tables, and subqueries are rejected.
- It may reference only columns of the same row — and not other generated columns.
- PostgreSQL supports
STORED only; there are no VIRTUAL generated columns before version 18.
- You cannot assign the value by hand, only via
DEFAULT.
ALTER TABLE orders
ADD COLUMN age_days int
GENERATED ALWAYS AS (now() - created_at) STORED;
Gotcha: lower() is marked IMMUTABLE, yet its result depends on the database LC_COLLATE. If you later change the collation, the stored email_norm values must be rebuilt by hand — the server will not recompute them for you.
When a trigger or a view is better
A generated column is great for a pure, row-local formula. Outside that:
- A
BEFORE INSERT/UPDATE trigger — when you need data from other tables, non-deterministic values (now()), or complex logic. You pay by maintaining the invariant yourself.
- A
VIEW — when the derived value is only needed at read time and should not consume storage or participate in an index. It is computed on the fly and always fresh.
CREATE VIEW order_totals AS
SELECT id, amount, amount * (1 + tax_rate) AS total
FROM orders;
Rule of thumb: deterministic, single-row formula — use a generated column; external data or manual writes — a trigger; read-only with no storage — a view.
Differences in other engines
- MySQL/MariaDB: support both
STORED and VIRTUAL (the default is VIRTUAL). Syntax is total AS (amount * 1.2) STORED; both kinds can be indexed.
- ClickHouse: the analog is
MATERIALIZED columns; they are not returned by SELECT * and are computed only on insert.
In PostgreSQL, stick to STORED and IMMUTABLE expressions, and the derived value stays consistent without a single line of application code.
A derived value — tax, an order total, a normalized email — tends to get duplicated across an application and drift over time. A generated column computes it inside the table, so the value stays consistent with its source data and lives in exactly one place.
What GENERATED ALWAYS AS does
A generated column is a column whose value the database computes from other columns of the same row, using a fixed expression. In PostgreSQL it is always
STORED: the result is physically written to disk like an ordinary column and recomputed whenever the source fields are inserted or updated.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.00You cannot write to
totaldirectly — that is the server's job: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"A single source of truth
The real win is an invariant at the schema level. Every client — backend, ETL job, an ad-hoc
psqlsession — sees the same value, and nobody can forget to recompute it. This is handy for normalization: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 '));Email lookups are now case- and whitespace-insensitive, and you no longer normalize the column in every query.
Indexing a generated column
Because the value is
STORED, you can index it like any other column — often cleaner than an expression index on the raw formula.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;A range query on
totaluses the index directly, with no re-evaluation ofamount * (1 + tax_rate).Restrictions to keep in mind
The expression must be deterministic and depend only on the current row:
IMMUTABLEfunctions are allowed.now(),random(), references to other tables, and subqueries are rejected.STOREDonly; there are noVIRTUALgenerated columns before version 18.DEFAULT.-- Fails: now() is not IMMUTABLE ALTER TABLE orders ADD COLUMN age_days int GENERATED ALWAYS AS (now() - created_at) STORED; -- ERROR: generation expression is not immutableGotcha:
lower()is markedIMMUTABLE, yet its result depends on the databaseLC_COLLATE. If you later change the collation, the storedemail_normvalues must be rebuilt by hand — the server will not recompute them for you.When a trigger or a view is better
A generated column is great for a pure, row-local formula. Outside that:
BEFORE INSERT/UPDATEtrigger — when you need data from other tables, non-deterministic values (now()), or complex logic. You pay by maintaining the invariant yourself.VIEW— when the derived value is only needed at read time and should not consume storage or participate in an index. It is computed on the fly and always fresh.CREATE VIEW order_totals AS SELECT id, amount, amount * (1 + tax_rate) AS total FROM orders;Rule of thumb: deterministic, single-row formula — use a generated column; external data or manual writes — a trigger; read-only with no storage — a view.
Differences in other engines
STOREDandVIRTUAL(the default isVIRTUAL). Syntax istotal AS (amount * 1.2) STORED; both kinds can be indexed.MATERIALIZEDcolumns; they are not returned bySELECT *and are computed only on insert.In PostgreSQL, stick to
STOREDandIMMUTABLEexpressions, and the derived value stays consistent without a single line of application code.