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

Question

PostgreSQL支持类似于MS SQL Server的计算列吗？

我在文档中找不到任何相关信息，但是许多其他数据库管理系统都包含了这个功能，所以我可能漏掉了什么？

- Mike Chamberlain

---

使用横向子查询表达式（Postgres功能），您可以轻松地为每行添加更多列。 - Victor

Answer 1

Postgres 12或更新版本

STORED生成列是在Postgres 12中引入的-根据SQL标准定义，并由一些RDBMS（包括DB2，MySQL和Oracle）实现。或者类似于SQL Server的"计算列"。

简单示例：

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

fiddle

VIRTUAL 生成的列可能会在下一次迭代中出现。（尚未在 Postgres 16 中实现）。

相关：

• 函数调用的属性表示法导致错误

Postgres 11 或更早版本

在 Postgres 11 及之前的版本中不支持"生成的列"。
您可以使用函数和属性表示法（tbl.col）来模拟 VIRTUAL 生成的列，它看起来和工作方式都很像虚拟生成的列。这是一个语法上的奇特之处，出于历史原因而存在，并恰好适用于此情况。这个相关答案中有代码示例：

• 将常见查询存储为列？

这个表达式（看起来像一列）不包含在SELECT * FROM tbl中。你总是需要明确地列出它。

如果函数是IMMUTABLE的话，也可以通过匹配的expression index来支持。就像这样：

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

替代方案

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

"持久化"（STORED）计算列可以通过触发器以等效的方式实现。

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

- Erwin Brandstetter

---

这些解决方案在从Oracle迁移到Postgres时几乎没有用处（除非对没有测试用例的代码库进行大量代码更改）。从迁移的角度来看，有什么解决方案吗？ - happybuddha

@ErwinBrandstetter完成了。只是觉得这不值得一个新问题。http://stackoverflow.com/questions/39824244/migrating-virtual-columns-from-oracle-to-postgres。干杯 - happybuddha

该功能目前正在开发中：https://commitfest.postgresql.org/16/1443/ - r90t

有很多替代方法可以完成Postgres中的一些简单和常见操作，是否有人可以解释如何选择其中一种方法？ - cryanbhu

@cryanbhu：这取决于您的设置和要求的详细信息。您可以提出一个新问题，并提供必要的信息。 - Erwin Brandstetter

Answer 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 而不用担心它的工作原理...只需获得良好的性能。该视图不会复制原始表中的任何数据，因此从内存和性能方面来说都很好。但请注意，您无法更新 / 插入视图，而只能更新 / 插入源表，但如果您真的想这样做，我相信您可以使用规则将插入和更新重定向到源表（我对最后一点可能是错误的，因为我从未尝试过）。

编辑：看来如果查询涉及竞争索引，则规划器可能根本不使用表达式索引。选择似乎取决于数据。

- dan-man

---

请问您能否解释一下或者举个例子说明“如果查询涉及竞争索引”是什么意思？ - davidtgq

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

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

- Elmer

---

触发器实际何时触发？我运行了上述代码并执行了以下操作：`insert into computed values(1, 2); insert into computed values(4, 8); commit;select * from computed;`，但它只返回了：1 2和4 8。 - happybuddha

尝试执行以下代码：insert into computed(one) values(1); insert into computed(one) values(4); commit; select * from computed;，two列的值将会自动计算！ - Elmer

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

db<>fiddle演示

- Lukasz Szozda

---

博客文章：https://www.2ndquadrant.com/en/blog/generated-columns-in-postgresql-12/ - Christophe Roussy

Answer 5

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

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

1. 这个太简单了，我怀疑这不是你要找的。
2. 显然它不是动态的，你只需运行一次即可。但没有障碍将其放入触发器中。

- ďobo

Answer 6

创建一个空的虚拟列的示例

,(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

- Ahmed Awad

Answer 7

我有一段代码可以运行，并使用术语“calculated”，尽管我们在PADB上运行，但并非纯粹的postgresSQL。

以下是它的使用方式：

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    ;

- Wired604

---

PADB是什么？ - Gherman

ParAccel分析数据库很老但很好用...https://en.wikipedia.org/wiki/ParAccel - Wired604

但是这与Postgres相关的问题有什么关系呢？当然，许多支持计算列的数据库也是存在的。 - Gherman

啊抱歉，我没有花时间回到上下文…… PADB 是基于PostgreSQL的！ - Wired604

Answer 8

一个带有Check约束的轻量级解决方案：

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

- cinereo

---

这与计算列的概念有何关联？您能解释一下吗？ - Erwin Brandstetter

同意，这与直接相关性不大。但是它可以替代一个简单的情况，当你只需要执行类似于field as 1 persisted的操作时。 - cinereo

一个描述确实会很好。我认为这个答案是，如果计算可以使用默认子句完成，那么您可以使用默认值和检查约束来防止任何人更改该值。 - Ross Bradbury

@Ross Bradbury：同意，但这只适用于插入操作。如果依赖列得到更新，那么它将不起作用。 - Stefan Steiger