How Do Computed Columns Work in PostgreSQL, and What Alternatives Exist in Older Versions?

Computed columns in PostgreSQL

PostgreSQL supports computed columns, also known as computed columns, virtual columns, derived columns, or generated columns, allowing you to create columns whose values ​​are derived from other columns or expressions.

Supported in Postgres 12 or later

Starting from version 12, PostgreSQL introduces STORED generated columns, which are compliant with SQL standards and can also be found in DBMS such as DB2, MySQL and Oracle.

Imulation in Postgres 11 or older

PostgreSQL versions before 11 do not natively support generated columns. However, it is possible to simulate VIRTUAL generated columns using functions with attribute notation. This syntax is similar to the behavior of virtual generated columns and provides similar functionality.

Alternatives

In addition to computed columns, PostgreSQL provides other mechanisms to achieve similar results:

• Views: Views can be defined to contain calculation expressions, but their results are not materialized and must be evaluated on every query.
• Triggers: Triggers can be used to "persist" calculated values, simulating the behavior of STORED generated columns.
• Materialized Views: Introduced in Postgres 9.3, materialized views allow storing precomputed results for faster access.

The above is the detailed content of How Do Computed Columns Work in PostgreSQL, and What Alternatives Exist in Older Versions?. For more information, please follow other related articles on the PHP Chinese website!