PostgreSQL 中的计算/计算/虚拟/派生列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8250389/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Computed / calculated / virtual / derived columns in PostgreSQL
提问by Mike Chamberlain
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.
PostgreSQL 是否支持计算/计算列，如 MS SQL Server？我在文档中找不到任何内容，但由于此功能包含在许多其他 DBMS 中，我想我可能会遗漏一些东西。
回答by Erwin Brandstetter
Up to Postgres 11 generated columnsare 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.
最多不支持Postgres 11生成的列- 正如 SQL 标准中定义的那样，并由一些 RDBMS 实现，包括 DB2、MySQL 和 Oracle。也不是 SQL Server的类似“计算列”。
STOREDgenerated columns are introduced with Postgres 12. Trivial example:
STOREDPostgres 12引入了生成的列。简单的例子：
CREATE TABLE tbl (
int1 int
, int2 int
, product bigint GENERATED ALWAYS AS (int1 * int2) STORED
);
db<>fiddle here
db<>在这里摆弄
VIRTUALgenerated columns may come with one of the next iterations. (Not in Postgres 13, yet) .
VIRTUAL生成的列可能伴随下一次迭代之一。（尚未在 Postgres 13 中）。
Related:
有关的：
Until then, you can emulate VIRTUALgenerated columns with a functionusing 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:
在此之前，您可以VIRTUAL使用一个使用属性表示法( )的函数来模拟生成的列，该函数的外观和工作方式与虚拟生成列非常相似。由于历史原因，这在 Postgres 中存在一些语法上的奇怪之处，并且恰好适合这种情况。这个相关的答案有代码示例：tbl.col
The expression (looking like a column) is not included in a SELECT * FROM tbl, though. You always have to list it explicitly.
但是，表达式（看起来像一列）不包含在 a 中SELECT * FROM tbl。您始终必须明确列出它。
Can also be supported with a matching expression index- provided the function is IMMUTABLE. Like:
也可以使用匹配的表达式索引来支持- 如果函数是IMMUTABLE. 喜欢：
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.
或者，您可以使用 a 实现类似的功能VIEW，可选地与表达式索引结合使用。然后SELECT *可以包含生成的列。
"Persisted" (STORED) computed columns can be implemented with triggersin a functionally identical way.
“持久化” ( STORED) 计算列可以以功能相同的方式使用触发器实现。
Materialized viewsare a closely related concept, implemented since Postgres 9.3.
In earlier versions one can manage MVs manually.
物化视图是一个密切相关的概念，自 Postgres 9.3 开始实施。
在早期版本中，可以手动管理 MV。
回答by dan-man
YES you can!!The solution should be easy, safe, and performant...
是的你可以！！解决方案应该简单、安全且高效……
I'm new to postgresql, but it seems you can create computed columns by using an expression index, paired with a view(the view is optional, but makes makes life a bit easier).
我是 postgresql 的新手，但似乎您可以使用表达式 index创建计算列，并与视图配对（视图是可选的，但使生活更轻松）。
Suppose my computation is md5(some_string_field), then I create the index as:
假设我的计算是md5(some_string_field)，那么我将索引创建为：
CREATE INDEX some_string_field_md5_index ON some_table(MD5(some_string_field));
Now, any queries that act on MD5(some_string_field)will use the index rather than computing it from scratch. For example:
现在，任何操作的查询MD5(some_string_field)都将使用索引而不是从头开始计算。例如：
SELECT MAX(some_field) FROM some_table GROUP BY MD5(some_string_field);
You can check this with explain.
您可以使用解释检查这一点。
However at this point you are relying on users of the table knowing exactly how to construct the column. To make life easier, you can create a VIEWonto an augmented version of the original table, adding in the computed value as a new column:
但是，此时您依赖于表的用户确切地知道如何构造列。为了让生活更轻松，您可以VIEW在原始表的增强版本上创建一个，将计算值添加为新列：
CREATE VIEW some_table_augmented AS
SELECT *, MD5(some_string_field) as some_string_field_md5 from some_table;
Now any queries using some_table_augmentedwill be able to use some_string_field_md5without worrying about how it works..they just get good performance. The view doesn't copy any data from the original table, so it is good memory-wise as well as performance-wise. Note however that you can't update/insert into a view, only into the source table, but if you really want, I believe you can redirect inserts and updates to the source table using rules(I could be wrong on that last point as I've never tried it myself).
现在任何使用的查询some_table_augmented都可以使用some_string_field_md5而无需担心它是如何工作的......他们只是获得了良好的性能。该视图不会从原始表中复制任何数据，因此它在内存和性能方面都很好。但是请注意，您不能更新/插入视图，只能更新/插入到源表中，但如果您真的想要，我相信您可以使用规则将插入和更新重定向到源表（我在最后一点上可能是错误的我自己从未尝试过）。
Edit:it seems if the query involves competing indices, the planner engine may sometimes not use the expression-index at all. The choice seems to be data dependant.
编辑：似乎如果查询涉及竞争索引，规划器引擎有时可能根本不使用表达式索引。选择似乎取决于数据。
回答by Elmer
One way to do this is with a trigger!
一种方法是使用触发器！
CREATE TABLE computed(
one SERIAL,
two INT NOT NULL
);
CREATE OR REPLACE FUNCTION computed_two_trg()
RETURNS trigger
LANGUAGE plpgsql
SECURITY DEFINER
AS $BODY$
BEGIN
NEW.two = NEW.one * 2;
RETURN NEW;
END
$BODY$;
CREATE TRIGGER computed_500
BEFORE INSERT OR UPDATE
ON computed
FOR EACH ROW
EXECUTE PROCEDURE computed_two_trg();
The trigger is fired before the row is updated or inserted. It changes the field that we want to compute of NEWrecord and then it returns that record.
在更新或插入行之前触发触发器。它更改了我们要计算的NEW记录字段，然后返回该记录。
回答by Lukasz Szozda
PostgreSQL 12 supports generated columns:
PostgreSQL 12 支持生成的列：
PostgreSQL 12 Beta 1 Released!
Generated Columns
PostgreSQL 12 allows the creation of generated columns that compute their values with an expression using the contents of other columns. This feature provides stored generated columns, which are computed on inserts and updates and are saved on disk.Virtual generated columns, which are computed only when a column is read as part of a query, are not implemented yet.
生成的列
PostgreSQL 12 允许创建生成的列，这些列使用其他列的内容通过表达式计算它们的值。此功能提供存储的生成列，这些列在插入和更新时计算并保存在磁盘上。虚拟生成列仅在作为查询的一部分读取列时计算，尚未实现。
A generated column is a special column that is always computed from other columns. Thus, it is for columns what a view is for tables.
生成的列是一个特殊的列，它总是从其他列计算出来。因此，对于列而言，视图对于表而言是一样的。
CREATE TABLE people (
...,
height_cm numeric,
height_in numeric GENERATED ALWAYS AS (height_cm * 2.54) STORED
);
回答by Wired604
I have a code that works and use the term calculated, I'm not on postgresSQL pure tho we run on PADB
我有一个有效的代码并使用计算的术语，我不是在纯粹的 postgresSQL 上，但我们在 PADB 上运行
here is how it's used
这是它的使用方式
create table some_table as
select category,
txn_type,
indiv_id,
accum_trip_flag,
max(first_true_origin) as true_origin,
max(first_true_dest ) as true_destination,
max(id) as id,
count(id) as tkts_cnt,
(case when calculated tkts_cnt=1 then 1 else 0 end) as one_way
from some_rando_table
group by 1,2,3,4 ;
回答by ?obo
Well, not sure if this is what You mean but Posgres normally support "dummy" ETL syntax. I created one empty column in table and then needed to fill it by calculated records depending on values in row.
好吧，不确定这是否是您的意思，但 Posgres 通常支持“虚拟”ETL 语法。我在表中创建了一个空列，然后需要根据行中的值通过计算记录来填充它。
UPDATE table01
SET column03 = column01*column02; /*e.g. for multiplication of 2 values*/
- It is so dummy I suspect it is not what You are looking for.
- Obviously it is not dynamic, you run it once. But no obstacle to get it into trigger.
- 它太假了，我怀疑这不是你要找的。
- 显然它不是动态的，你运行一次。但是没有任何障碍可以触发它。
回答by cinereo
A lightweight solution with Check constraint:
带有检查约束的轻量级解决方案：
CREATE TABLE example (
discriminator INTEGER DEFAULT 0 NOT NULL CHECK (discriminator = 0)
);
