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

#1 楼

最多不支持Postgres 11生成的列-这是SQL标准中定义的，并且由某些RDBMS（包括DB2，MySQL和Oracle）实现。也没有SQL Server的类似“计算列”。

STORED生成的列未随Postgres 12引入。一个简单的示例：

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

在此处查找错误

VIRTUAL生成的列可能带有下一个迭代之一。 （尚未在Postgres 13中）。

相关的内容：函数调用的属性符号给出错误
带有使用属性表示法（VIRTUAL）的函数，该函数看起来和工作起来都非常像虚拟生成的列。出于历史原因，Postgres中存在某种语法上的奇怪之处，并且恰好适合这种情况。这个相关的答案有代码示例：


将常见查询存储为列？

tbl.col中不包含表达式（看起来像列）。您始终必须明确列出它。

也可以由匹配的表达式索引支持-前提是函数SELECT * FROM tbl。像：

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

替代方案

或者，您可以使用IMMUTABLE实现类似的功能，还可以选择与表达式索引结合使用。然后VIEW可以包含生成的列。

“ Persisted”（SELECT *）计算的列可以使用触发器以功能相同的方式实现。

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

#2 楼

是的你可以！！该解决方案应该简单，安全且高效...

我是Postgresql的新手，但是看来您可以通过使用与视图配对的表达式索引来创建计算列（该视图是可选，但会使生活更轻松。）

我的计算是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);

您可以通过explain进行检查。

但是，此时，您依靠表的用户确切地知道构造列。为了使生活更轻松，您可以在原始表的增强版本上创建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，而无需担心其工作原理。它们只是获得了良好的性能。该视图不会复制原始表中的任何数据，因此从内存角度和性能角度来说都是很好的选择。但是请注意，您不能只在源表中更新/插入视图，但是如果您确实需要，我相信您可以使用规则将插入和更新重定向到源表（在最后一点上我可能是错的，因为我从未尝试过自己。）

编辑：如果查询涉及竞争索引，似乎计划程序引擎有时可能根本不使用expression-index。选择似乎取决于数据。

#3 楼

一种方法是使用触发器！

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记录的字段，然后返回该记录。

#4 楼

PostgreSQL 12支持生成的列：


PostgreSQL 12 Beta 1发布了！

生成的列

PostgreSQL 12允许创建生成的列使用其他列的内容通过表达式计算其值。此功能提供了存储的生成的列，这些列在插入和更新时计算并保存在磁盘上。虚拟生成的列（仅当将列作为查询的一部分读取时才计算）尚未实现。





生成的列

生成的列是一个特殊的列，总是从其他列计算得出。因此，表的视图就是列。

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

小提琴演示

#5 楼

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

UPDATE table01
SET column03 = column01*column02; /*e.g. for multiplication of 2 values*/

它太假了，我怀疑它不是您想要的。
显然，它不是动态的，只需运行一次即可。但是没有任何障碍可以触发它。

#6 楼

我有一个可以使用并使用“计算”​​一词的代码，我不是在postgresSQL上运行，而是在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    ;

#7 楼

具有Check约束的轻量级解决方案：

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