PostgreSQL Generated Columns: STORED Computed Values, Search Vectors, and Automated Denormalization

Generated columns automate computed values at the database level — eliminating the trigger boilerplate needed to keep derived data in sync. A search_vector column can update automatically when title or description changes. A full_name column can combine first and last name without application code. An amount_usd column can normalize different currencies.

This guide covers when to use generated columns, the patterns that work, and the constraints to know before reaching for them.

Syntax and Constraints

-- Generated column syntax
CREATE TABLE products (
  id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name        TEXT NOT NULL,
  description TEXT,
  price_cents INTEGER NOT NULL,
  tax_rate    NUMERIC(5,4) NOT NULL DEFAULT 0.08,

  -- STORED: computed and physically stored on disk
  -- VIRTUAL: computed on read (PostgreSQL only supports STORED as of 16)
  price_with_tax_cents INTEGER GENERATED ALWAYS AS (
    ROUND(price_cents * (1 + tax_rate))
  ) STORED,

  -- String concatenation
  slug_prefix TEXT GENERATED ALWAYS AS (
    lower(regexp_replace(name, '[^a-zA-Z0-9]+', '-', 'g'))
  ) STORED
);

-- Constraints on generated columns:
-- ✅ Can reference other columns in the same row
-- ✅ Can use most deterministic functions (lower, round, regexp_replace, etc.)
-- ❌ Cannot reference other tables (no subqueries, no joins)
-- ❌ Cannot use non-deterministic functions (NOW(), random(), gen_random_uuid())
-- ❌ Cannot INSERT/UPDATE a generated column directly (it's always computed)
-- ❌ Cannot reference another generated column (no chaining)

Pattern 1: Full-Text Search Vector

The most common use case — keeping tsvector in sync without triggers:

CREATE TABLE blog_posts (
  id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  workspace_id UUID NOT NULL REFERENCES workspaces(id),
  title       TEXT NOT NULL,
  excerpt     TEXT,
  content     TEXT NOT NULL,
  author_name TEXT NOT NULL,
  tags        TEXT[] NOT NULL DEFAULT '{}',

  -- Auto-generated search vector: updates whenever any source column changes
  -- to_tsvector('english', ...) is deterministic — valid in generated column
  search_vector TSVECTOR GENERATED ALWAYS AS (
    setweight(to_tsvector('english', coalesce(title, '')),       'A') ||
    setweight(to_tsvector('english', coalesce(author_name, '')), 'B') ||
    setweight(to_tsvector('english', coalesce(excerpt, '')),     'C') ||
    setweight(to_tsvector('english', coalesce(content, '')),     'D')
  ) STORED,

  created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- GIN index on the generated column for fast full-text search
CREATE INDEX idx_blog_posts_search ON blog_posts USING GIN(search_vector);

-- Query: full-text search with ranking
SELECT
  id,
  title,
  excerpt,
  ts_rank(search_vector, query) AS rank
FROM blog_posts,
  plainto_tsquery('english', 'postgresql performance') AS query
WHERE workspace_id = $1
  AND search_vector @@ query
ORDER BY rank DESC
LIMIT 10;

Compare to the trigger approach — generated column is cleaner and always in sync:

-- ❌ Old approach: trigger to maintain tsvector (more code, easier to get wrong)
CREATE OR REPLACE FUNCTION update_blog_search_vector()
RETURNS TRIGGER LANGUAGE plpgsql AS $$
BEGIN
  NEW.search_vector :=
    setweight(to_tsvector('english', coalesce(NEW.title, '')), 'A') ||
    setweight(to_tsvector('english', coalesce(NEW.author_name, '')), 'B') ||
    setweight(to_tsvector('english', coalesce(NEW.excerpt, '')), 'C') ||
    setweight(to_tsvector('english', coalesce(NEW.content, '')), 'D');
  RETURN NEW;
END;
$$;

CREATE TRIGGER trg_blog_posts_search
BEFORE INSERT OR UPDATE ON blog_posts
FOR EACH ROW EXECUTE FUNCTION update_blog_search_vector();

-- ✅ Generated column: zero trigger code, same result

Pattern 2: User Full Name

CREATE TABLE users (
  id         UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  first_name TEXT NOT NULL,
  last_name  TEXT NOT NULL,

  -- Automatically kept in sync — no trigger needed
  full_name TEXT GENERATED ALWAYS AS (
    first_name || ' ' || last_name
  ) STORED,

  email      TEXT NOT NULL UNIQUE,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Index on full_name for search
CREATE INDEX idx_users_full_name ON users(lower(full_name) text_pattern_ops);

-- Query: search by name prefix
SELECT id, full_name, email
FROM users
WHERE lower(full_name) LIKE lower($1) || '%'
  AND workspace_id = $2
LIMIT 10;

Pattern 3: Normalized Computed Values

-- Invoice totals computed from line items would require a trigger or join
-- But for row-level computations, generated columns work perfectly:

CREATE TABLE invoice_line_items (
  id           UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  invoice_id   UUID NOT NULL REFERENCES invoices(id),
  unit_price   INTEGER NOT NULL,   -- Cents
  quantity     INTEGER NOT NULL DEFAULT 1,
  discount_pct NUMERIC(5,2) NOT NULL DEFAULT 0,  -- 0-100%

  -- Computed at insert/update time
  subtotal    INTEGER GENERATED ALWAYS AS (
    unit_price * quantity
  ) STORED,

  discount_amount INTEGER GENERATED ALWAYS AS (
    ROUND(unit_price * quantity * discount_pct / 100)
  ) STORED,

  total INTEGER GENERATED ALWAYS AS (
    ROUND(unit_price * quantity * (1 - discount_pct / 100))
  ) STORED
);

-- Now you can SUM total directly without re-computing
SELECT
  invoice_id,
  SUM(total) AS invoice_total,
  SUM(discount_amount) AS total_discounts
FROM invoice_line_items
WHERE invoice_id = $1
GROUP BY invoice_id;

Pattern 4: Data Normalization and Bucketing

CREATE TABLE user_activity (
  id           UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id      UUID NOT NULL REFERENCES users(id),
  session_duration_seconds INTEGER NOT NULL,
  page_count   INTEGER NOT NULL,
  occurred_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),

  -- Derived bucketing — useful for analytics and partitioning
  duration_bucket TEXT GENERATED ALWAYS AS (
    CASE
      WHEN session_duration_seconds < 60    THEN 'short'
      WHEN session_duration_seconds < 600   THEN 'medium'
      WHEN session_duration_seconds < 3600  THEN 'long'
      ELSE 'very_long'
    END
  ) STORED,

  -- Date truncation for time-series grouping
  activity_date DATE GENERATED ALWAYS AS (
    (occurred_at AT TIME ZONE 'UTC')::date
  ) STORED,

  activity_hour INTEGER GENERATED ALWAYS AS (
    EXTRACT(HOUR FROM occurred_at AT TIME ZONE 'UTC')::integer
  ) STORED
);

-- Fast analytics without date arithmetic in queries
SELECT duration_bucket, COUNT(*), AVG(page_count)
FROM user_activity
WHERE activity_date BETWEEN $1 AND $2
GROUP BY duration_bucket;

-- Index for time-series queries
CREATE INDEX idx_activity_date ON user_activity(user_id, activity_date);

Prisma Integration

Prisma handles generated columns correctly — you can read them but cannot write to them:

// schema.prisma
model BlogPost {
  id           String   @id @default(dbgenerated("gen_random_uuid()")) @db.Uuid
  workspaceId  String   @db.Uuid
  title        String
  excerpt      String?
  content      String
  authorName   String
  tags         String[]

  // Generated column: read-only in Prisma
  // Use @default to signal it's never set by application code
  searchVector Unsupported("tsvector")?

  createdAt    DateTime @default(now())
  updatedAt    DateTime @updatedAt
}

model User {
  id        String   @id @default(dbgenerated("gen_random_uuid()")) @db.Uuid
  firstName String
  lastName  String
  fullName  String   // GENERATED ALWAYS AS (first_name || ' ' || last_name) STORED
  email     String   @unique
}

// lib/search.ts — full-text search using generated search_vector
import { prisma } from "@/lib/prisma";

export async function searchBlogPosts(
  workspaceId: string,
  query: string,
  limit = 10
): Promise<Array<{ id: string; title: string; excerpt: string | null; rank: number }>> {
  if (!query.trim()) return [];

  return prisma.$queryRaw`
    SELECT
      id,
      title,
      excerpt,
      ts_rank(search_vector, plainto_tsquery('english', ${query})) AS rank
    FROM blog_posts
    WHERE workspace_id = ${workspaceId}::uuid
      AND search_vector @@ plainto_tsquery('english', ${query})
    ORDER BY rank DESC
    LIMIT ${limit}
  `;
}

// Create/update blog post — don't set searchVector (generated automatically)
export async function createBlogPost(data: {
  workspaceId: string;
  title: string;
  excerpt?: string;
  content: string;
  authorName: string;
}) {
  return prisma.blogPost.create({
    data: {
      ...data,
      // searchVector is automatically computed by PostgreSQL
      // Do NOT include it here — insert will fail
    },
  });
}

Generated vs Trigger: When to Use Each

Storage Overhead

Generated columns physically store their values on disk. Factor this into your schema:

-- Check storage impact of generated columns
SELECT
  attname,
  pg_catalog.format_type(atttypid, atttypmod) AS type,
  attgenerated                                  -- 's' = stored, '' = not generated
FROM pg_attribute
WHERE attrelid = 'blog_posts'::regclass
  AND attnum > 0
  AND NOT attisdropped;

-- Estimate row size with generated columns
SELECT
  pg_size_pretty(pg_total_relation_size('blog_posts'))  AS total_size,
  pg_size_pretty(pg_relation_size('blog_posts'))         AS table_size,
  n_live_tup                                             AS live_rows
FROM pg_stat_user_tables
WHERE relname = 'blog_posts';

A tsvector for a 500-word document is typically 2–5KB. For a table with 1M posts, that's 2–5GB of generated column storage. GIN indexes on tsvector add another 30–50% on top of that. Plan your storage accordingly.

See Also

• PostgreSQL Full-Text Search Advanced
• PostgreSQL Triggers and Audit Logging
• PostgreSQL Indexing Advanced Patterns
• PostgreSQL Schema Design for SaaS
• Prisma Advanced Patterns and Extensions

---

Working With Viprasol

Generated columns eliminate an entire category of trigger boilerplate — particularly for full-text search vectors, which are notoriously easy to get out of sync when maintained with application code or poorly-written triggers. Our team uses generated columns for tsvector maintenance, computed display fields, and analytics bucketing as standard practice.

What we deliver:

• search_vector TSVECTOR GENERATED ALWAYS AS with setweight A/B/C/D for title/author/excerpt/content
• GIN index on generated tsvector for fast full-text queries
• Full name concatenation for user display with prefix-search index
• Invoice line item totals (subtotal/discount/total) as generated integers
• Prisma schema annotations and TypeScript search function with $queryRaw

Talk to our team about your PostgreSQL schema design →

Or explore our cloud and database services.