Using GENERATED data columns

You are probably used to the idea that a column can have a default value that’s set by a function; this is how we use sequences to set column values in tables. The SQL Standard provides a new syntax for this, which is referred to as GENERATED ... AS IDENTITY. PostgreSQL supports this, but we won’t discuss that here.

We can also use views to dynamically calculate new columns as if the data had been stored. PostgreSQL 12+ allows the user to specify that columns can be generated and stored in the table automatically, which is easier and faster than writing a trigger to do this. This is a very important performance and usability feature since we can store data that may take significant time to calculate, so this is much better than just using views. We refer to this feature as GENERATED ALWAYS, which also follows the SQL Standard syntax.

How to do it…

Let’s start with an example table:

CREATE TABLE example
( id ...