Generated Columns

CREATE TABLE products (
    id             bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name           text NOT NULL,
    price          numeric(10,2) NOT NULL,
    tax_rate       numeric(4,2)  NOT NULL DEFAULT 0.19,
    price_with_tax numeric(10,2)
        GENERATED ALWAYS AS (price * (1 + tax_rate)) STORED
);

INSERT INTO products (name, price) VALUES ('Widget', 100.00);

SELECT name, price, tax_rate, price_with_tax FROM products;

  name  | price  | tax_rate | price_with_tax
--------+--------+----------+----------------
 Widget | 100.00 |     0.19 |         119.00

CREATE TABLE persons (
    id        bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    firstname text NOT NULL,
    lastname  text NOT NULL,
    fullname  text GENERATED ALWAYS AS (firstname || ' ' || lastname) STORED
);

CREATE TABLE articles (
    id      bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    title   text NOT NULL,
    body    text NOT NULL,
    search  tsvector GENERATED ALWAYS AS (
        setweight(to_tsvector('german', title), 'A') ||
        setweight(to_tsvector('german', body),  'B')
    ) STORED
);

CREATE INDEX articles_search_idx ON articles USING gin (search);

CREATE TABLE customers (
    id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    email       text NOT NULL,
    email_lower text GENERATED ALWAYS AS (lower(email)) STORED
);

CREATE UNIQUE INDEX customers_email_lower_idx
ON customers (email_lower);

-- now(): VOLATILE → verboten
created_age int GENERATED ALWAYS AS (
    EXTRACT(EPOCH FROM now() - created_at)
) STORED;
-- ERROR: generation expression is not immutable

-- Zugriff auf andere Tabellen: verboten
customer_name text GENERATED ALWAYS AS (
    (SELECT name FROM customers WHERE id = customer_id)
) STORED;
-- ERROR: cannot use subquery in generation expression

-- Verweis auf andere Generated Columns: verboten
col_b text GENERATED ALWAYS AS ('B') STORED,
col_c text GENERATED ALWAYS AS (col_b || 'C') STORED
-- ERROR: cannot use generated column "col_b" in column generation expression

CREATE TABLE products (
    id             bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    price          numeric(10,2) NOT NULL,
    tax_rate       numeric(4,2)  NOT NULL DEFAULT 0.19,
    price_with_tax numeric(10,2)
        GENERATED ALWAYS AS (price * (1 + tax_rate)) VIRTUAL
);

CREATE INDEX products_price_with_tax_idx ON products (price_with_tax);

CREATE INDEX products_price_with_tax_idx
ON products ((price * (1 + tax_rate)));