PostgreSQL中的计算/计算/虚拟/派生列 [英] Computed / calculated / virtual / derived columns in PostgreSQL

问题描述

PostgreSQL是否支持计算/计算列，例如MS SQL Server?我在文档中找不到任何内容，但是由于许多其他DBMS都包含此功能，所以我认为我可能会遗漏某些东西.

例如: http://msdn.microsoft.com/zh-cn/library/ms191250.aspx

解决方案

最多支持Postgres 11 生成的列-SQL标准中定义并由包括DB2，MySQL和甲骨文.也没有类似的SQL Server 计算列" .

STORED生成的列与 Postgres 12 一起引入.简单的例子:

CREATE TABLE tbl (
  int1    int
, int2    int
, product bigint GENERATED ALWAYS AS (int1 * int2) STORED
);

db<>小提琴此处

VIRTUAL生成的列可能带有下一个迭代之一. (尚未在Postgres 13中).

相关:

• 函数调用的属性符号给出了错误

---

在此之前，您可以使用属性符号(tbl.col)使用功能来模拟VIRTUAL生成的列外观和工作方式都非常类似于虚拟生成的列.出于历史原因，Postgres中存在某种语法上的奇怪之处，并且恰好适合这种情况.此相关答案包含代码示例:

• 将常见查询存储为列?

不过，SELECT * FROM tbl中不包含表达式(看起来像一列).您总是必须明确列出它.

也可以通过匹配的表达式索引支持-提供功能是IMMUTABLE.喜欢:

CREATE FUNCTION col(tbl) ... AS ...  -- your computed expression here
CREATE INDEX ON tbl(col(tbl));

替代项

或者，您可以使用 VIEW ，可选地与表达式索引结合.然后SELECT *可以包含生成的列.

可以使用触发器来实现

"Persisted"(STORED)计算列以功能相同的方式.

材料化视图是一个紧密相关的概念，http://msdn.microsoft.com/en-us/library/ms191250.aspx

解决方案

Up to Postgres 11 generated columns are not supported - as defined in the SQL standard and implemented by some RDBMS including DB2, MySQL and Oracle. Nor the similar "computed columns" of SQL Server.

STORED generated columns are introduced with Postgres 12. Trivial example:

CREATE TABLE tbl (
  int1    int
, int2    int
, product bigint GENERATED ALWAYS AS (int1 * int2) STORED
);

db<>fiddle here

VIRTUAL generated columns may come with one of the next iterations. (Not in Postgres 13, yet) .

Related:

• Attribute notation for function call gives error

---

Until then, you can emulate VIRTUAL generated columns with a function using attribute notation (tbl.col) that looks and works much like a virtual generated column. That's a bit of a syntax oddity which exists in Postgres for historic reasons and happens to fit the case. This related answer has code examples:

• Store common query as column?

The expression (looking like a column) is not included in a SELECT * FROM tbl, though. You always have to list it explicitly.

Can also be supported with a matching expression index - provided the function is IMMUTABLE. Like:

CREATE FUNCTION col(tbl) ... AS ...  -- your computed expression here
CREATE INDEX ON tbl(col(tbl));

Alternatives

Alternatively, you can implement similar functionality with a VIEW, optionally coupled with expression indexes. Then SELECT * can include the generated column.

"Persisted" (STORED) computed columns can be implemented with triggers in a functionally identical way.

Materialized views are a closely related concept, implemented since Postgres 9.3.
In earlier versions one can manage MVs manually.

这篇关于PostgreSQL中的计算/计算/虚拟/派生列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！