Can (aggregate) functions be used to define a column?

Question

Assume a table like this one:

a | b | total
--|---|------
1 | 2 |  3
4 | 7 | 11
…

CREATE TEMPORARY TABLE summedup (
    a double precision DEFAULT 0
  , b double precision DEFAULT 0
  --, total double precision
);
INSERT INTO summedup (a, b) VALUES (1, 2);
INSERT INTO summedup (a, b) VALUES (4, 7);

SELECT a, b, a + b as total FROM summedup;

It's easy to sum up the first two columns on SELECT.

But does Postgres (9.6) also support the ability to define total as the sum of the other two columns? If so:

What is the syntax?
What is this type of operation called (aggregates typically sum up cells over multiple rows, not columns.)

score 3 · Accepted Answer · 2019-04-15 13:24:48Z

3

What you are looking for is typically called a "computed column".

Postgres 9.6 does not support that (Postgres 12 - to be released in Q4 2019 - will).

But for such a simple sum, I wouldn't bother storing redundant information.

If you don't want to repeat the expression, create a view.

edited Apr 15, 2019 at 13:24

answered Apr 15, 2019 at 13:12

user330315

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

2 Comments

Tim Biegeleisen Over a year ago

"storing redundant information" would I guess assume that the computed column in the forthcoming release of Postgres would not be virtual (which it can be, e.g. in MySQL) +1.

user330315 Over a year ago

@TimBiegeleisen: both. You can define it as "virtual" or "stored".

Mihai Blidaru · Accepted Answer · 2019-04-15 13:19:25Z

0

I think what you want is a View.

CREATE VIEW table_with_sum AS 
 SELECT id, a, b, a + b as total FROM summedup;

then you can query the view for the sum.

SELECT total FROM table_with_sum where id=5;

The View does not store the sum for each row, the totalcolumn is computed every time you query the View. If your goal is to make your query more efficient, this will not help.

There is an other way: add the column to the table and create triggers for update and insert that update the total column every time a row is modified.

answered Apr 15, 2019 at 13:19

Mihai Blidaru

1205 bronze badges

Collectives™ on Stack Overflow

Can (aggregate) functions be used to define a column?

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related