Search code examples
sqlpostgresqlgenerated-columns

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

Does PostgreSQL support computed columns like MS SQL Server?

I can't find anything in the docs, but the feature is included in many other DBMS so maybe I am missing something?

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:

    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:

    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.