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

generated-columns, postgresql, sql

Solution

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.

Problem

Original source

Related problems

• Store common query as column?