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

PostgreSQL 是否支持计算/计算列，如 MSSQLServer？我在文档中找不到任何东西，但是由于这个特性包含在许多其他 DBMS 中，我想我可能遗漏了一些东西。

例句: http://msdn.microsoft.com/en-us/library/ms191250.aspx

160716 次浏览
小开
最佳答案

12年或更早

使用 Postgres 12引入了 STORED生成的列 ——这是 SQL 标准中定义的，并由包括 DB2、 MySQL 和 Oracle 在内的一些 RDBMS 实现。或 SQLServer 的类似 “计算列”。

小例子:

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

小提琴

VIRTUAL生成的列可能会在下一个迭代中出现(目前在 Postgres 15中还没有)。

相关阅读:

11岁或以上

直到 Postgres 11“生成的列”是不支持的。
您可以使用 看起来和工作方式很像虚拟生成的列的 属性表示法(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 *可以包含生成的列。

“持久化”(STORED)计算列可以用 触发器以功能等效的方式实现。

物化视图 是一个相关的概念，由 Postgres 9.3开始实施。
在早期版本中，可以手动管理 MV。

小开

带有 Check 约束的轻量级解决方案:

CREATE TABLE example (
discriminator INTEGER DEFAULT 0 NOT NULL CHECK (discriminator = 0)
);
小开

一种方法就是用扳机！

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();

在更新或插入行之前触发触发器。它更改要计算的 NEW记录的字段，然后返回该记录。

小开

是的，你可以! 解决方案应该是简单、安全和有效的..。

我刚接触 postgreql，但似乎可以使用 表达式索引表达式索引和 风景创建计算列(视图是可选的，但使生活变得更容易一些)。

假设我的计算是 md5(some_string_field)，那么我创建索引如下:

CREATE INDEX some_string_field_md5_index ON some_table(MD5(some_string_field));

现在，任何作用于 MD5(some_string_field)的查询都将使用索引，而不是从头开始计算。例如:

SELECT MAX(some_field) FROM some_table GROUP BY MD5(some_string_field);

你可以用 解释一下检查一下。

但是，此时您依赖于表的用户确切地知道如何构造列。为了方便起见，您可以在原始表的增强版本上创建一个 VIEW，并将计算值作为一个新列添加进来:

CREATE VIEW some_table_augmented AS
SELECT *, MD5(some_string_field) as some_string_field_md5 from some_table;

现在，任何使用 some_table_augmented的查询都可以使用 some_string_field_md5，而不用担心它是如何工作的。.他们只是表现得很好。视图不会从原始表中复制任何数据，因此在内存方面和性能方面都很好。然而请注意，您不能更新/插入到一个视图中，只能插入到源表中，但是如果您真的想要，我相信您可以使用 规矩重定向插入和更新到源表(我可能在最后一点上是错误的，因为我自己从来没有尝试过)。

编辑: 似乎如果查询包含竞争索引，规划引擎有时可能根本不使用表达式索引。这种选择似乎取决于数据。

小开

我有一个工作的代码，并使用术语计算，我不是纯粹的后 SQL，但我们运行在 PADB

以下是它的使用方法

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    ;
小开

好吧，不确定这是你的意思，但 Posgres 通常支持“虚拟”ETL 语法。 我在表中创建了一个空列，然后需要根据行中的值通过计算记录来填充它。

UPDATE table01
SET column03 = column01*column02; /*e.g. for multiplication of 2 values*/
  1. 它是如此愚蠢，我怀疑它不是你在寻找什么。
  2. 显然它不是动态的，你运行它一次，但是没有障碍让它进入触发器。
小开

PostgreSQL 12支持生成的列:

PostgreSQL12Beta 1发布！

生成的列

PostgreSQL 12允许创建生成的列，这些列使用使用其他列的内容的表达式计算它们的值。这个特性提供了存储的生成列，这些列在插入和更新时计算，并保存在磁盘上。虚拟生成的列(仅在将列作为查询的一部分读取时计算)尚未实现。

生成的列

生成的列是总是从其他列计算得到的特殊列。因此，对于列，视图就是对于表的视图。

CREATE TABLE people (
...,
height_cm numeric,
height_in numeric GENERATED ALWAYS AS (height_cm * 2.54) STORED
);

Db < > 小提琴演奏

小开

关于创建空虚拟列的示例

,(SELECT *
From (values (''))
A("virtual_col"))

关于创建两个具有值的虚拟列的示例

SELECT *
From (values (45,'Completed')
, (1,'In Progress')
, (1,'Waiting')
, (1,'Loading')
) A("Count","Status")
order by "Count" desc

提交答案