PostgreSQL - Generate Columns

When working with databases, there are scenarios where you need a column’s value to be automatically computed based on other columns. In PostgreSQL, this can be achieved through generated columns. These special columns are calculated based on an expression using other columns in the table. The value of a generated column is updated automatically whenever any of the referenced columns change, ensuring that the derived value is always accurate.

What are Generated Columns?

A generated column is a computed column whose value is derived from other columns within the same row. You cannot directly insert or update data into these columns because their values are determined by the expression defined when the table is created. In PostgreSQL, generated columns can only use immutable functions and cannot involve subqueries.

Types of Generated Columns

In theory, generated columns can be of two types:

• Stored Generated Columns: These columns are computed and stored in the database. The values are calculated during INSERT or UPDATE operations and occupy physical storage. Stored generated columns function similarly to materialized views, except they are automatically updated.
• Virtual Generated Columns: These columns are calculated when they are accessed and do not occupy storage. They are similar to standard views, as the values are computed on the fly during SELECT queries.

Note: As of the latest PostgreSQL versions, only stored generated columns are supported.

Syntax of PostgreSQL for CREATE TABLE using Generated Columns as follows:

CREATE TABLE table_name (
  column_name_1 datatype(length)  column constrain (if any),
  column_name_2 datatype(length)  column constrain (if any) ,
   .
   .
   .
   .
  column_name datatype GENERATED ALWAYS AS (expression) STORED
);

• datatype: Specifies the data type of the generated column.
• expression: Defines the formula or expression used to calculate the value of the generated column.
• STORED: Indicates that the generated column is physically stored in the database.

PostgreSQL Generate Columns Example

Let us take a look at an example to Generate Columns in PostgreSQL to better understand the concept.

 CREATE TABLE  Addition (
    number_1 int,
   number_2 int,
   number_3 int,
   add int  GENERATED ALWAYS AS (number_1 + number_2 + number_3) STORED
)
 INSERT INTO  Addition(number_1, number_2, number_3) VALUES  (1,2,3) , (8,-9,2) , (7,1 ,NULL);
 TABLE Addition; 

Output:

The 'total_sum' column will display the sum of the three numbers for each row. If a NULL value is encountered, the result will also be NULL unless you handle it in the expression using functions like 'COALESCE()'.

Important Points About PostgreSQL Generated Columns

• Generated columns automatically update their values whenever referenced columns change.
• The expression for a generated column can only use immutable functions. Functions that produce different results for the same inputs over time (e.g., 'random()', 'now()') cannot be used.
• You can apply constraints (e.g., NOT NULL, UNIQUE) to generated columns just like any other column.