Sometimes it becomes handy when you can auto-generate a column based on an expression. For this there is the concept of generated column. Those columns can be either of type “stored” (the derived value is computed when the column is written) or “virtual” (the derived value is computed when the column is being read). Currently PostgreSQL only supports the “stored” generated columns, but there are certainly use cases for this. Let’s have a look at how that looks like.

As usual, we’ll start with a simple table to play with:

postgres=# create table t1 ( product text, price int, amount_sold int, amount_returned int ); 
CREATE TABLE
postgres=# insert into t1 values ('product1', 5, 1000, 10 );
INSERT 0 1
postgres=#

What we can use a generated column for is to auto-calculate the total price for all sold products. Without a generated column we would need a trigger for that (for simplicity will use integers for that, we do not need to be that precise here):

postgres=# alter table t1 add column total_price int generated always as (price*amount_sold) stored;
ALTER TABLE
postgres=# \d t1
                                          Table "public.t1"
     Column      |  Type   | Collation | Nullable |                     Default                      
-----------------+---------+-----------+----------+--------------------------------------------------
 product         | text    |           |          | 
 price           | integer |           |          | 
 amount_sold     | integer |           |          | 
 amount_returned | integer |           |          | 
 total_price     | integer |           |          | generated always as (price * amount_sold) stored

Having that in place, the total price is automatically calculated and there nothing else to do:

postgres=# select total_price from t1;
 total_price 
-------------
        5000
(1 row)

Another use case is to automatically calculate the percentage of returned products:

postgres=# alter table t1 add column percentage_returned int generated always as (amount_returned/(amount_sold/100)) stored;
ALTER TABLE
postgres=# select percentage_returned from t1;
 percentage_returned 
---------------------
                   1
(1 row)

postgres=#

For new (and updated) products this works, of course, as well:

postgres=# insert into t1 values ('product2', 33, 800, 600 );
INSERT 0 1
postgres=# select * from t1;
 product  | price | amount_sold | amount_returned | total_price | percentage_returned 
----------+-------+-------------+-----------------+-------------+---------------------
 product1 |     5 |        1000 |              10 |        5000 |                   1
 product2 |    33 |         800 |             600 |       26400 |                  75
(2 rows)

Updating generated columns will through an error, which is how it must work:

postgres=# update t1 set percentage_returned = 0;
ERROR:  column "percentage_returned" can only be updated to DEFAULT
DETAIL:  Column "percentage_returned" is a generated column.

You can even combine that with column level privileges to give other users or roles just access to these generated columns, but not the base columns:

postgres=# create user u with password 'u';
CREATE ROLE
postgres=# grant select(total_price,percentage_returned) on t1 to u;
GRANT
postgres=# \c postgres u
You are now connected to database "postgres" as user "u".
postgres=> select total_price,percentage_returned from t1;
 total_price | percentage_returned 
-------------+---------------------
        5000 |                   1
       26400 |                  75
(2 rows)

postgres=> select product from t1;
ERROR:  permission denied for table t1
postgres=>

What you cannot do, is to create a generated column which uses another generated column in the expression:

postgres=# \c postgres postgres
You are now connected to database "postgres" as user "postgres".
postgres=# alter table t1 add column money_lost int generated always as (total_price-((total_price/100)*percentage_returned)) stored;
ERROR:  cannot use generated column "total_price" in column generation expression
DETAIL:  A generated column cannot reference another generated column.
postgres=#

A nice little feature for plenty of use cases.

Post Views: 1,528
Thumbnail [60x60]
by
Daniel Westermann