PostgreSQL:创建一个生成的列 [英] PostgreSQL: creating a generated column
问题描述
目标:
我的目标是创建一个名为 paymentPrice
的新列这是 receiptPrice
, platformFee
, delivererFee
(
My goal is to create a new column called paymentPrice
which is the sum of receiptPrice
, platformFee
, delivererFee
(which needs to display the sum of the current values).
通过阅读我认为使用生成列的文档将是实现此目标的最佳方法。
From reading the docs I thought using a generated column would be thebest way to achieve this.
语法:
ALTER TABLE
"orders"
ADD
"paymentPrice" FLOAT GENERATED ALWAYS AS (orders."receiptPrice" + orders."platformFee" + orders."delivererFee") VIRTUAL;'
错误:
我当前的语法如下错误,但我不知道自己在做什么错
My current syntax results in the following error, but I can't figure out what I'm doing wrong
error: syntax error at or near "("
推荐答案
如上所述,生成的列将可用在Postgres 12中。
As noted above in the comments - generated columns will be available in Postgres 12.
可以使用版本< 12:
It is possible to fake a generated column with a function in versions < 12:
https:/ /www.db-fiddle.com/f/21FtTGSuTXzZxoQX9CRUZf/0
CREATE TABLE orders (
receiptPrice INT,
platformFee INT,
delivererFee INT
);
CREATE OR REPLACE FUNCTION paymentPrice(_order orders)
RETURNS integer AS $$
SELECT ( _order.receiptPrice + _order.platformFee + _order.delivererFee)
$$
STABLE
LANGUAGE SQL;
SELECT paymentPrice(orders) FROM orders;
我想如果有其他工具依赖它的话,它的用例就是我在哪里使用 https://github.com/graphile/postgraphile 这样的工具),或者是否应该使用查询不太冗长。
I guess a use case for this would be, if some other tooling depends on it (use cases for me where tools like https://github.com/graphile/postgraphile) or if the queries should be less verbose.
这篇关于PostgreSQL:创建一个生成的列的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!