Does PostgreSQL support computed / calculated columns, like MS SQL Server? I can’t find anything in the docs, but as this feature is included in many other DBMSs I thought I might be missing something.
Eg: http://msdn.microsoft.com/en-us/library/ms191250.aspx
Advertisement
Answer
Up to Postgres 11 generated columns are not supported – as defined in the SQL standard and implemented by some RDBMS including DB2, MySQL and Oracle. Nor the similar “computed columns” of SQL Server.
STORED generated columns are introduced with Postgres 12. Trivial example:
CREATE TABLE tbl ( int1 int , int2 int , product bigint GENERATED ALWAYS AS (int1 * int2) STORED );
db<>fiddle here
VIRTUAL generated columns may come with one of the next iterations. (Not in Postgres 14, yet).
Related:
Until then, 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 identical way.
Materialized views are a closely related concept, implemented since Postgres 9.3.
In earlier versions one can manage MVs manually.