How Can I Emulate Computed Columns in PostgreSQL?

Simulating Computed Columns within PostgreSQL

Unlike MS SQL Server, PostgreSQL doesn't natively support computed columns. However, several methods effectively replicate this functionality.

PostgreSQL 12 and Later

PostgreSQL 12 introduced STORED generated columns, mirroring the behavior of SQL Server's computed columns. These columns are physically stored within the table, readily accessible during queries.

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

PostgreSQL 11 and Earlier

For older PostgreSQL versions, simulating virtual generated columns requires functions and attribute notation.

Function Syntax:

<code class="language-sql">CREATE FUNCTION col(tbl) RETURNS type AS 'expression';</code>

Usage:

<code class="language-sql">SELECT tbl.col, ... FROM tbl;</code>

This approach mimics a virtual generated column; however, it's not included in SELECT *.

Alternative Approaches

• Views: Construct a view incorporating the computed column. Using SELECT * retrieves both original and computed columns.
• Triggers: Implement persistent computed columns by creating triggers to automatically update the computed column whenever its source columns change.
• Materialized Views: For frequently queried computed columns, materialized views pre-compute and store results, optimizing performance.

Important Considerations:

• STORED generated columns (PostgreSQL 12 ) are included in SELECT *.
• Functions emulating virtual generated columns must be defined as IMMUTABLE to enable expression indexes.
• Materialized views are ideal for persistent computed columns requiring frequent access.

The above is the detailed content of How Can I Emulate Computed Columns in PostgreSQL?. For more information, please follow other related articles on the PHP Chinese website!