Postgres Generated Columns: Stored vs Virtual (New in PostgreSQL 18)

A generated column is a column whose value Postgres calculates from other columns in the same row. You never insert or update it yourself — it's always consistent with its inputs.

CREATE TABLE order_lines (
  id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  unit_cents  integer NOT NULL,
  quantity    integer NOT NULL,
  total_cents integer GENERATED ALWAYS AS (unit_cents * quantity) STORED
);

INSERT INTO order_lines (unit_cents, quantity) VALUES (1250, 3);
SELECT total_cents FROM order_lines;  -- 3750

STORED vs VIRTUAL

Postgres has supported STORED generated columns since version 12. PostgreSQL 18 added VIRTUAL ones — and made virtual the default when you don't specify.

	STORED	VIRTUAL (Postgres 18+)
When computed	On INSERT/UPDATE, saved to disk	Every time it's read
Disk space	Uses space	None
Write cost	Slightly higher	None
Read cost	Free	Computed on read
Can be indexed	Yes	No (as of Postgres 18)
Default in Postgres 18	No	Yes

ALTER TABLE users
  ADD COLUMN email_lower text GENERATED ALWAYS AS (lower(email)) STORED;

ALTER TABLE users
  ADD COLUMN display_name text GENERATED ALWAYS AS (first_name || ' ' || last_name) VIRTUAL;

Rule of thumb: STORED if you'll filter, sort or index by it; VIRTUAL for cheap derived values you just display.

Note: adding a STORED column to a big table rewrites the whole table, which locks it. Adding a VIRTUAL one doesn't. (Postgres Migrations on Large Tables)

Great uses

Case-insensitive lookups:

ALTER TABLE users ADD COLUMN email_lower text
  GENERATED ALWAYS AS (lower(email)) STORED;
CREATE UNIQUE INDEX users_email_lower_key ON users (email_lower);

(An expression index on lower(email) does the same without a column; the generated column makes queries and ORMs simpler.)

Full-text search vectors:

ALTER TABLE articles ADD COLUMN search tsvector
  GENERATED ALWAYS AS (
    setweight(to_tsvector('english', coalesce(title, '')), 'A') ||
    setweight(to_tsvector('english', coalesce(body, '')), 'B')
  ) STORED;
CREATE INDEX articles_search_idx ON articles USING gin (search);

(Postgres Full-Text Search, Hybrid Search in Postgres)

Pulling a field out of JSONB so it can be indexed and queried normally:

ALTER TABLE events ADD COLUMN user_id bigint
  GENERATED ALWAYS AS ((payload->>'user_id')::bigint) STORED;

(Postgres JSONB)

The rules

• The expression must be immutable: same inputs, same output. now(), random() and most time-zone-dependent functions aren't allowed. (to_tsvector with an explicit configuration like 'english' is fine.)
• It can only use columns from the same row — no subqueries, no other tables.
• It can't reference another generated column.
• You can't write to it: INSERT or UPDATE with a value is an error (use DEFAULT or leave it out).
• Generated columns can't be part of the partition key and have some limits with logical replication — check the docs for your version.

Generated column vs view vs trigger

• Generated column: derived from the same row, always consistent, simplest.
• View: derived values across tables, or logic you'd rather not store. (Postgres Materialized Views)
• Trigger: needs other tables, non-immutable functions, or side effects. More power, more to maintain. (Postgres Triggers)

With ORMs

Tell your ORM the column is generated so it doesn't try to write it: Drizzle has .generatedAlwaysAs(...), and Prisma can map it as a read-only field in recent versions or via raw SQL migrations. (Drizzle vs Prisma)

The summary

• GENERATED ALWAYS AS (expr) STORED | VIRTUAL computes a column from the same row.
• Postgres 18 adds VIRTUAL (and makes it the default); use STORED when you need an index.
• Expressions must be immutable and same-row only.

---

EasySpawn runs a current PostgreSQL right next to your app, so features like virtual generated columns are there when you need them — and Claude Code can write the migration. See how it works or join the waitlist.

Related: Database Indexes · Postgres Index Types · Database Normalization · What Is PostgreSQL?