How Has PostgreSQL's Support for Computed Columns Evolved?

PostgreSQL Computed Columns: Review

As a powerful relational database management system, PostgreSQL provides a wide range of data operation and management functions. However, computed columns (also known as computed columns, virtual columns, derived columns, or generated columns) have been missing in their functionality. This article takes an in-depth look at support for computed columns in PostgreSQL and examines its evolution across versions.

PostgreSQL 11 and earlier

Prior to PostgreSQL 11, the database did not directly support computed columns. However, users can simulate virtual computed columns using functions with attribute notation (tbl.col). This technique provides a workaround similar to virtual generated columns. However, its use requires explicit listing of the expression in the SELECT query.

PostgreSQL 12 and higher

With the advent of PostgreSQL 12, the database introduces STORED generated columns, which are consistent with the SQL standard and consistent with implementations in DBMS such as DB2, MySQL, and Oracle. These calculated columns are stored in the table's data and automatically updated based on the specified expression.

Consider the following example:

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

In this example, the "product" column is a STORED generated column that calculates the product of int1 and int2.

Alternatives

In versions of PostgreSQL that do not directly support generated columns, alternative methods can be used:

• Views (VIEWs): Views can simulate the behavior of computed columns by defining queries that produce the required column values.
• Expression Indexes: Expression indexes can improve the performance of queries involving computed columns by creating indexes on their expressions.
• Materialized Views: Materialized views are precomputed versions of queries that can improve query performance. They function like calculated columns in that they provide precomputed values.

Conclusion

PostgreSQL’s support for computed columns has improved significantly across its versions. Although computed columns were not explicitly included before PostgreSQL 11, the database provided workarounds using function and property notation. PostgreSQL 12 introduces STORED generated columns, enabling support for computed columns in the database itself. These improvements provide users with additional flexibility and performance optimizations in data operations.

The above is the detailed content of How Has PostgreSQL's Support for Computed Columns Evolved?. For more information, please follow other related articles on the PHP Chinese website!