Introduction to PostgreSQL Generated Columns

In PostgreSQL, a generated column is a special type of column whose values are automatically calculated based on expressions or values from other columns.

A generated column is referred to as acomputed column in the SQL Server or avirtual column in Oracle.

There are two kinds of generated columns:

• Stored: A stored generated column is calculated when it is inserted or updated and occupies storage space.
• Virtual: A virtual generated column is computed when it is read and does not occupy storage space.

A virtual generated column is like aview, whereas a stored generated column is similar to amaterialized view. Unlike a material view, PostgreSQL automatically updates data for stored generated columns.

PostgreSQL currently implements only stored generated columns.

Defining generated columns

Typically, you define a generated column whencreating a table with the following syntax:

CREATE TABLE table_name(   ...,   column_name type GENERATED ALWAYS AS (expression )STORED | VIRTUAL,   ...);

In this syntax:

• column_name: Specify the name of the generated column.
• type: Specify the data type for the column.
• expression: Provide an expression that returns values for the calculated column.
• STORED keyword: Indicate that the data of the generated column is physically stored in the table.
• VIRTUAL keyword: Indicate that the data of the generated column is computed when queried, not stored physically.

To add a generated column to a table, you can use theALTER TABLE … ADD COLUMN statement:

ALTER TABLE table_nameADD COLUMN column_nametype GENERATED ALWAYS AS (expression) STORED;

When defining an expression for a generated column, ensure that it meets the following requirements:

• The expression can only use immutable functions and cannot involvesubqueries or reference anything beyond the current row. For example, the expression cannot use theCURRENT_TIMESTAMP function.
• The expression cannot reference another generated column or a system column, excepttableoid.

A generated column cannot have a default value or an identity definition. Additionally, it cannot be a part of the partition key.

PostgreSQL Generated Column examples

Let's explore some examples of using generated columns.

1) Concatenating columns

First, create a new table calledcontacts:

CREATE TABLE contacts(   idSERIAL PRIMARY KEY,   first_nameVARCHAR(50)NOT NULL,   last_nameVARCHAR(50)NOT NULL,   full_nameVARCHAR(101)GENERATED ALWAYS AS (first_name|| ' ' || last_name) STORED,   emailVARCHAR(300)UNIQUE);

Second, insert rows into thecontacts table. The values of thefull_name column will be automatically updated from the values in thefirst_name andlast_name columns:

INSERT INTO contacts(first_name, last_name, email)VALUES   ('John','Doe','john.doe@example.com'),   ('Jane','Doe','jane.doe@example.com')RETURNING*;

Output:

id | first_name | last_name | full_name |              email----+------------+-----------+-----------+---------------------------------  1 | John       | Doe       | John Doe  | john.doe@example.com  2 | Jane       | Doe       | Jane Doe  | jane.doe@example.com(2 rows)

2) Calculating net prices

First, create a table calledproducts that stores the product information:

CREATE TABLE products (    id SERIAL PRIMARY KEY,    name VARCHAR(100)NOT NULL,    list_price DECIMAL(10, 2)NOT NULL,    tax DECIMAL(5, 2)DEFAULT 0,    discount DECIMAL(5, 2)DEFAULT 0,    net_price DECIMAL(10, 2)GENERATED ALWAYS AS ((list_price+ (list_price* tax / 100)) - (list_price * discount / 100)) STORED);

In theproducts table, thenet_price column is a generated column whose values are calculated based on the list price, tax, and discount with the following formula:

list_price= list_price+ (list_price* tax/ 100))- (list_price* discount/ 100)

Second, insert rows into theproducts table:

INSERT INTO products (name,list_price, tax, discount)VALUES    ('A', 100.00, 10.00, 5.00),    ('B', 50.00, 8.00, 0.00),    ('C', 120.00, 12.50, 10.00)RETURNING *;

Output:

id | name | list_price |  tax  | discount | net_price----+------+------------+-------+----------+-----------  1 | A    |     100.00 | 10.00 |     5.00 |    105.00  2 | B    |      50.00 |  8.00 |     0.00 |     54.00  3 | C    |     120.00 | 12.50 |    10.00 |    123.00(3 rows)

Summary

• Use generated columns to automate calculations within your table.

Last updated onMarch 25, 2024

On this page

• Introduction to PostgreSQL Generated Columns
• PostgreSQL Generated Column examples
• Summary