Skip to main content
Mapagam

Working with Generated Columns

1. Understanding Generated Columns

PropertyDetail
STOREDComputed at write, persisted (PG supports STORED only)
VIRTUALNot yet implemented in core PG
ExpressionMust 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

ConstraintRule
Must be IMMUTABLENo now(), random(), etc.
No subqueriesOnly column refs from same row
No INSERT/UPDATECannot supply explicit value
Cannot reference other generated colsUntil 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.

Was this article helpful?

Related Cheat Sheets