Defining generated columns with PostgreSQL and TypeORM

SQL

On this blog, we’ve covered a variety of different column types. So far, we’ve performed various operations on said columns such as and to modify the data directly. In this article, we cover generated columns that work differently.

Generated columns in PostgreSQL

We call the above columns generated because PostgreSQL automatically computes their data based on predefined expressions. However, the crucial thing is that we can’t insert any data into them directly.

Generated columns became available in PostgreSQL 12 in 2019

The SQL standard consists of two types of generated columns:

  • virtual,
  • stored.

Virtual generated columns

The idea behind virtual generated columns is that it occupies no disk storage. Therefore, it is computed on the fly when requested. A good example is returning the value with the statement. Unfortunately, PostgreSQL currently doesn’t implement virtual generated columns.

Stored generated columns

The stored generated columns occupy storage in the same way as a regular column. However, instead of computing the value every time it is requested, PostgreSQL does so only when the row is modified.

To define a stored generated column, we need to use the followed by expression and the keyword :

Above, we’ve defined the column to be a combination of the and . It is common to refer to other columns while defining a generated column. That being said, a generated column can’t refer to another generated column.

Let’s test the above table by inserting a row:

As we can see in the above table, PostgreSQL automatically figured out the value for the column.

Types of expressions we can use

There are a few different types of expressions we can use when defining a generated column. For example, besides operating on text as in the previous example, we can also do so with numbers.

Besides simple operations, we can also use functions. The crucial thing is that they have to be immutable. An immutable function can’t modify the database and must return the same result given the same arguments.

An interesting example of a function that is not immutable is . Let’s try to use it instead of the operator:

ERROR: generation expression is not immutable

Surprisingly, can yield different results based on the database configuration. Unfortunately, it makes it not immutable.

If you want to know more about timezones, check out Managing date and time with PostgreSQL and TypeORM

Generated columns with TypeORM

Fortunately, TypeORM started supporting generated columns for PostgreSQL a few days ago.

Please remember that we need to use the sign with the column names   and above. Otherwise, PostgreSQL would transform them to and .

An issue with updating entities

There is an important catch when using generated columns. Consider the following example:

There is a good chance that our application allows us to update posts with the method. With it, we expect the users of our API to send all of the properties of the entity. Unfortunately, appending the in the body of the request will cause an error.

We can quickly solve the above issue with the library and the decorator:

With it, the excludes the field from the body of the request.

If you want to know more about the library, check out API with NestJS #5. Serializing the response with interceptors

Simulating virtual generated columns with getters

While PostgreSQL does not support virtual generated columns, there is a simple way of simulating them when using TypeORM and classes. To do that, we can use a getter.

There is a good chance you are using the class-transformer library along with TypeORM. If you want to return the value along with the rest of the data, you need to use the decorator:

When doing the above, the database does not store the value in a column. Instead, it is computed every time on the fly when accessed. The crucial thing to keep in mind is that PostgreSQL won’t know anything about the value, so we can’t use it in any SQL queries.

Summary

The generated columns can come in handy when we often do a set of operations with our data. Instead of retrieving the data and performing the calculations, we can optimize this process by operating on the data beforehand. We need to keep in mind that we do the above optimization at the cost of our and operations.

Another considerable use case for generated columns is when we want to refactor our database. For example, we can define generated columns if we’re going to change some of our columns but keep the backward compatibility.

All of the above make the generated columns a feature that is worth knowing. Especially since TypeORM just recently started supporting it for PostgreSQL.

Subscribe
Notify of
guest
2 Comments
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
GisCat
GisCat
1 month ago

Hello,
I tried to use it in my project but it doesn’t work, is the feature still available in PostgreSQL ?

ABegon
ABegon
22 days ago

Hi,
You said it’s needed to use simple quote ' around column name in the asExpression property. When I tried that interpreted the column names as string, making all the fullName values equal to ‘firstName lastName’. It make sense because single quote is for string in sql.

I replaced the simple quotes by double quotes " in the asExpression and it work.