Computed at write, persisted (PG supports STORED only)
VIRTUAL
Not yet implemented in core PG
Expression
Must be immutable, deterministic
2. Creating Stored Generated Columns
CREATE TABLE rectangles ( w numeric NOT NULL, h numeric NOT NULL, area numeric GENERATED ALWAYS AS (w * h) STORED);
3. Using Generated Columns with Expressions
CREATE TABLE users ( first_name text, last_name text, full_name text GENERATED ALWAYS AS (first_name || ' ' || last_name) STORED);
4. Using Generated Columns with JSON
CREATE TABLE events ( payload jsonb NOT NULL, user_id bigint GENERATED ALWAYS AS ((payload->>'user_id')::bigint) STORED, tsv tsvector GENERATED ALWAYS AS (to_tsvector('english', payload->>'description')) STORED);
5. Creating Indexes on Generated Columns
CREATE INDEX events_user_id_idx ON events(user_id);CREATE INDEX events_tsv_idx ON events USING gin(tsv);
6. Understanding Generated Column Limitations
Constraint
Rule
Must be IMMUTABLE
No now(), random(), etc.
No subqueries
Only column refs from same row
No INSERT/UPDATE
Cannot supply explicit value
Cannot reference other generated cols
Until PG 17 supports it
7. Altering Generated Column Expression
-- Drop and re-add (no direct ALTER for expression before PG 16)ALTER TABLE users DROP COLUMN full_name;ALTER TABLE users ADD COLUMN full_name text GENERATED ALWAYS AS (initcap(first_name || ' ' || last_name)) STORED;
8. Dropping Generated Column
ALTER TABLE rectangles DROP COLUMN area;
Note: Dropping a generated column drops dependent indexes automatically.