PostgreSQL 中的计算/计算/虚拟/派生列

本文介绍了PostgreSQL 中的计算/计算/虚拟/派生列的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

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

例如:msdn.microsoft/en-us/图书馆/ms191250.aspx

解决方案

不支持 Postgres 11 生成的列 - 正如 SQL 标准中定义的那样，并由一些 RDBMS 实现，包括 DB2、MySQL 和甲骨文.也没有 SQL Server 类似的计算列".

STORED 生成的列由 Postgres 12 引入.小例子:

CREATE TABLE tbl (整数 1 整数, int2 int, product bigint GENERATED ALWAYS AS (i​​nt1 * int2) STORED);

db<>fiddle 这里

VIRTUAL 生成的列可能随下一次迭代之一出现.(尚未在 Postgres 14 中).

相关:

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

在那之前，您可以使用属性符号(tbl.col)，看起来和工作起来很像一个虚拟生成的列.由于历史原因，这在 Postgres 中存在一些语法上的奇怪之处，并且恰好适合这种情况.这个相关的答案有代码示例:

• 将常见查询存储为列?

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

也可以通过匹配的表达式索引来支持 - 前提是函数是IMMUTABLE.喜欢:

CREATE FUNCTION col(tbl) ... AS ... -- 你的计算表​​达式在这里在 tbl(col(tbl)) 上创建索引；

替代方案

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

坚持"(STORED) 计算列可以使用 触发器 以功能相同的方式.

物化视图是一个密切相关的概念，自 Postgres 9.3 起实施.早期版本可以手动管理MV.

Does PostgreSQL support computed / calculated columns, like MS SQL Server? I can't find anything in the docs, but as this feature is included in many other DBMSs I thought I might be missing something.

Eg: msdn.microsoft/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 14, 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.