PostgreSQL: creating a generated column

Question

Goal:

My goal is to create a new column called paymentPricewhich 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.

Syntax:

ALTER TABLE
   "orders"
ADD
   "paymentPrice" FLOAT GENERATED ALWAYS AS (orders."receiptPrice" + orders."platformFee" + orders."delivererFee") VIRTUAL;'

Error:

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

generated always as identity ( .... please refer to documentation at postgresql.org/docs/10/sql-createtable.html — Ravinder Reddy
– Ravinder Reddy, Commented Jul 16, 2019 at 11:25
This is currently not available in Postgres - computed columns will be available in Postgres 12 to be released in Q4 2019 for the time being I would simply create a view - why do you need that as a generated column? — user330315
– user330315, Commented Jul 16, 2019 at 11:44

madflow · Accepted Answer · 2020-03-24 19:32:27Z

2

As noted above in the comments - generated columns will be available in Postgres 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;

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.

edited Mar 24, 2020 at 19:32

answered Jul 16, 2019 at 12:03

madflow

8,7013 gold badges45 silver badges61 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

PostgreSQL: creating a generated column

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related