Does PostgreSQL Support Computed Columns Like SQL Server?

Computed columns in PostgreSQL

Q: Does PostgreSQL support calculated columns similar to those in MS SQL Server?

A: Yes, PostgreSQL 12 and later introduces STORED generated columns, which are similar to computed columns in SQL Server.

PostgreSQL 12 or higher

• Supports STORED generated columns and follows SQL standards.

• Example:

  <code class="language-sql">  CREATE TABLE tbl (
        int1    int,
        int2    int,
        product bigint GENERATED ALWAYS AS (int1 * int2) STORED
    );</code>

PostgreSQL 11 or lower

• does not directly support generated columns.

• The workaround is to use a function with attribute notation, which simulates a virtual generated column:

  <code class="language-sql">  CREATE FUNCTION col(tbl) ... AS ...  -- 计算表达式
    CREATE INDEX ON tbl(col(tbl));</code>

Alternatives

• View can be used to implement computed columns, but cannot be accessed via SELECT *.
• Trigger can be used to implement persistent (STORED) calculated columns.
• Materialized views provide more efficient options for persisting calculated columns.

The above is the detailed content of Does PostgreSQL Support Computed Columns Like SQL Server?. For more information, please follow other related articles on the PHP Chinese website!