Computed / calculated / virtual / derived / generated columns in PostgreSQL

by

Postgres 12 or newer

STORED generated columns are introduced with Postgres 12 – as defined in the SQL standard and implemented by some RDBMS including DB2, MySQL, and Oracle. Or the similar “computed columns” of SQL Server.

Trivial example:

CREATE TABLE tbl (
  int1    int
, int2    int
, product bigint GENERATED ALWAYS AS (int1 * int2) STORED
);

fiddle

VIRTUAL generated columns may come with one of the next iterations. (Not in Postgres 16, yet).

Related:

  • Attribute notation for function call gives error

Postgres 11 or older

Up to Postgres 11 “generated columns” are not supported.
You can emulate VIRTUAL generated columns with a function using attribute notation (tbl.col) that looks and works much like a virtual generated column. That’s a bit of a syntax oddity which exists in Postgres for historic reasons and happens to fit the case. This related answer has code examples:

  • Store common query as column?

The expression (looking like a column) is not included in a SELECT * FROM tbl, though. You always have to list it explicitly.

Can also be supported with a matching expression index – provided the function is IMMUTABLE. Like:

CREATE FUNCTION col(tbl) ... AS ...  -- your computed expression here
CREATE INDEX ON tbl(col(tbl));

Alternatives

Alternatively, you can implement similar functionality with a VIEW, optionally coupled with expression indexes. Then SELECT * can include the generated column.

“Persisted” (STORED) computed columns can be implemented with triggers in a functionally equivalent way.

Materialized views are a related concept, implemented since Postgres 9.3.
In earlier versions one can manage MVs manually.

Leave a Comment