SoFunction
Updated on 2025-04-08

Introduction to the implementation process of PostgreSQL column generation

The generated columns in PostgreSQL are special columns calculated from other columns. The generated column is different from a normal column. It is not a fixed value, but is determined by referring to the expressions of other columns in the table. Generating columns were introduced in the SQL standard (ISO/IEC 9075) and supported by mainstream RDBMS. PostgreSQL12 began to support generation columns.

Example

The following is a table that is used to demonstrate PostgreSQL generation columns:

CREATE TABLE Students (
  Id INTEGER PRIMARY KEY,
  FirstName VARCHAR(50),
  LastName VARCHAR(50),
  FullName VARCHAR(101) GENERATED ALWAYS AS (FirstName || ' ' || LastName) STORED
);

The last column in the above example is the generated column, and the PostgreSQL generated column must haveGENERATED ALWAYS(Usually other relational databases are optional). In addition, the end of the generated column must beSTORED. This specifies that the generated column needs to be stored (relative tovirtual, explained below), other relational databases are usually optional (the default value is virtual).

The syntax for generating columns is as follows:

<column_name> <datatype> GENERATED ALWAYS AS(expression) [STORED|VIRTUAL]

In the GENERATED ALWAYS AS (expression) Stored clause, use the existing column specification to generate a list expression to calculate the value for the generated column.

Now that we have created a table with generated columns, insert the data below:

INSERT INTO Students (Id, FirstName, LastName) 
VALUES (0001, 'Lucy', 'Green');
INSERT INTO Students (Id, FirstName, LastName) 
VALUES (0002, 'Aziz', 'Ahmad');
INSERT INTO Students (Id, FirstName, LastName) 
VALUES (0003, 'Zohan', 'Ahuja');
INSERT INTO Students (Id, FirstName, LastName) 
VALUES (0004, 'Homer', 'Presley');
INSERT INTO Students (Id, FirstName, LastName) 
VALUES (0005, 'Sally', 'Smith');

Note that we insert data without including the generated column, now view the data:

SELECT * FROM Students;

The result is:

 id | firstname | lastname |   fullname    
----+-----------+----------+---------------
  1 | Lucy      | Green    | Lucy Green
  2 | Aziz      | Ahmad    | Aziz Ahmad
  3 | Zohan     | Ahuja    | Zohan Ahuja
  4 | Homer     | Presley  | Homer Presley
  5 | Sally     | Smith    | Sally Smith

We see that the generated column contains the connection string with the values ​​of the FirstName and LastName columns. Generating list expressions is not only a string concatenation, but for example: In some scenarios, the actual price needs to be calculated based on the original price and the discount.

Stored vs Virtual

The generated column can be stored or virtual. The difference between the two is:

  • STORED (aka persistence): The column values ​​are stored in the table. When written (insert or updated), the storage column is recalculated and takes up storage space like a normal column.
  • VIRTUAL: Virtually generated columns do not take up storage space and are calculated only when read. PostgreSQL (14) supports only stored column generation.

Limitation description

There are many limitations for the generated column. For example, a generated expression cannot refer to another generated column. Furthermore, they can only use immutable functions and cannot use subqueries or reference anything other than the current line in any way. Specifically including:

  • Generating expressions can only use immutable functions and cannot use subqueries or reference anything other than the current line in any way.
  • The generated expression cannot refer to another generated column.
  • Generated expressions cannot refer to system columns (except table classes).
  • The generated column cannot have column default values ​​or identification definitions.
  • The generated column cannot be part of the partition key.
  • External tables can generate columns

This is the end of this article about the introduction of the PostgreSQL column generation implementation process. For more related PostgreSQL column generation content, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!