PostgreSQL Generated Columns: Virtual and Stored

A PostgreSQL generated column is computed from other columns in the same row. You cannot assign it an ordinary value directly; PostgreSQL computes it from its generation expression.

PostgreSQL 18 added virtual generated columns and made them the default. PostgreSQL 17 and earlier support only stored generated columns. If a schema must work across those versions, specify STORED explicitly.

Virtual vs. stored generated columns

Both kinds are recalculated from their source columns. A virtual column avoids storing the generated value, while a stored column uses disk space and avoids recalculating the expression on each read.

Syntax

In PostgreSQL 18 and later, use this column definition in a CREATE TABLE or ALTER TABLE statement:

column_name data_type
    GENERATED ALWAYS AS (generation_expression) [VIRTUAL | STORED]

If you omit the final keyword in PostgreSQL 18 or later, the column is VIRTUAL. To use the definition on PostgreSQL 17 or earlier, specify STORED:

total_amount numeric
    GENERATED ALWAYS AS (unit_price * quantity) STORED

Example: calculate an order line total

Create a table with a virtual column that multiplies each item’s price by its quantity:

CREATE TABLE order_item (
    item_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    goods text NOT NULL,
    unit_price numeric NOT NULL,
    quantity integer NOT NULL,
    total_amount numeric
        GENERATED ALWAYS AS (unit_price * quantity) VIRTUAL
);

Insert values for the ordinary columns. Do not provide a value for total_amount:

INSERT INTO order_item (goods, unit_price, quantity)
VALUES ('Apple', 5, 3), ('Peach', 4, 4);

Read the generated values:

SELECT item_id, goods, unit_price, quantity, total_amount
FROM order_item
ORDER BY item_id;

 item_id | goods | unit_price | quantity | total_amount
---------+-------+------------+----------+--------------
       1 | Apple |          5 |        3 |           15
       2 | Peach |          4 |        4 |           16
(2 rows)

If the source columns change, PostgreSQL recalculates the generated value. For example:

UPDATE order_item
SET quantity = 5
WHERE goods = 'Apple';

The Apple row’s total_amount is now 25. An INSERT or UPDATE cannot assign a regular value to a generated column; use DEFAULT if you include that column in the command.

Restrictions and choosing a type

Generation expressions must use immutable functions and operate only on the current row. They cannot contain subqueries, refer to other rows or tables, or reference another generated column. A generated column cannot be part of a partition key.

Virtual columns have an additional PostgreSQL 18 restriction: their data type and generation expression must use built-in types and functions. Choose STORED when you need a user-defined type or function, when you need compatibility with PostgreSQL 17 or earlier, or when storing the computed result is preferable to recomputing it on reads.

A generated column differs from a DEFAULT: a default is used when a row is inserted without a value for that column and can be overridden, while a generated column is always derived from its expression and is recalculated when its source columns change.

See PostgreSQL’s generated column documentation and the PostgreSQL 18 release notes for complete rules and version details.