Storing the result of a computation

PostgreSQL 12 has more features that can simplify development. In many cases, the content of a column has to be derived from some other column. Traditionally, this has been implemented using a trigger. However, writing a trigger needs some coding and manual work. PostgreSQL has a better solution to the problem.

Suppose we want to store data in kilometers and nautical miles. One nautical mile translates to 1.852 km. 

To make sure that nautical miles are always generated, the following syntax can be used:

test=# CREATE TABLE t_measurement (
 t timestamp, 
 km numeric, 
 nm numeric GENERATED ALWAYS AS (km * 1.852) STORED
);
CREATE TABLE

GENERATED ALWAYS AS is an elegant way to pre-calculate the content of a column. As you can see, the definition does exactly what we expect: 

test=# INSERT INTO t_measurement (t, km) VALUES (now(), 100) RETURNING *;
 t | km |  nm  
----------------------------+-----+---------
 2019-09-30 15:02:31.004481 | 100 | 185.200
(1 row)

INSERT 0 1

However, there is more than instantly meets the eye—GENERATED ALWAYS AS also ensures that the content of the column cannot be changed to ensure that the value is always correct:

test=# INSERT INTO t_measurement (t, km, nm) VALUES (now(), 100, 1000) RETURNING *;
ERROR: cannot insert into column "nm"
DETAIL: Column "nm" is a generated column.

Performance-wise, the new feature is also faster than the traditional method of using a trigger.