PostgreSQL Computed Columns: What Alternatives Exist?

PostgreSQL computed columns: exploring alternatives

PostgreSQL users often ask about the availability of computed columns, a feature common in other database management systems such as MS SQL Server. Although PostgreSQL does not yet natively support computed columns, it provides various alternatives to achieve similar functionality.

Storing generated columns: PostgreSQL 12 and later

PostgreSQL 12 introduces the concept of storing generated columns, which is compliant with SQL standards and supported by other RDBMS such as DB2, MySQL and Oracle. These columns are created using the STORED keyword and are calculated when data is retrieved or inserted.

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

Virtually generated columns: PostgreSQL 11 and earlier

For PostgreSQL 11 and earlier, true virtual generated columns are not supported. However, the user can simulate its behavior using functions with attribute notation. This method involves using the tbl.col syntax, which mimics the appearance and functionality of a virtually generated column.

<code class="language-sql">CREATE FUNCTION col(tbl) ... AS ...  -- 您的计算表达式在此处</code>

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

Alternatives

In addition to the above methods, PostgreSQL users can also consider other methods to achieve similar functions:

• Views: Views can be used to create virtually generated columns, allowing users to select them in SELECT * queries.
• Expression indexes: These indexes can be created on functions that compute derived values, providing performance benefits for queries involving these values.
• Materialized View: Materialized view stores the results of a query, providing a snapshot of the data at a specific point in time.
• Triggers: Triggers can be used to update or insert derived values ​​into a table.

While PostgreSQL may not currently support native computed columns, its flexible and powerful feature set provides various solutions to implement similar functionality to meet the various needs of its users.

The above is the detailed content of PostgreSQL Computed Columns: What Alternatives Exist?. For more information, please follow other related articles on the PHP Chinese website!