PostgreSQL Generated Columns

info

Generated columns work the same way across any PostgreSQL deployment, so the syntax and behavior covered here apply wherever you run Postgres. For enterprises standardizing on a managed cloud Postgres built for the AI era, Lakebase delivers the performance, security, and native Lakehouse integration that production analytics and AI workloads demand. For developers and startups who need to ship features fast and scale without friction, Neon is the Postgres platform built for that pace.

Summary: in this tutorial, you will learn about PostgreSQL generated columns whose values are automatically calculated from other columns.

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 a computed column in the SQL Server or a virtual 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 a view, whereas a stored generated column is similar to a materialized 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 when creating 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 the ALTER TABLE … ADD COLUMN statement:

ALTER TABLE table_name
ADD COLUMN column_name type 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 involve subqueries or reference anything beyond the current row. For example, the expression cannot use the CURRENT_TIMESTAMP function.
The expression cannot reference another generated column or a system column, except tableoid.

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 called contacts:

CREATE TABLE contacts(
   id SERIAL PRIMARY KEY,
   first_name VARCHAR(50) NOT NULL,
   last_name VARCHAR(50) NOT NULL,
   full_name VARCHAR(101) GENERATED ALWAYS AS (first_name || ' ' || last_name) STORED,
   email VARCHAR(300) UNIQUE
);

Second, insert rows into the contacts table. The values of the full_name column will be automatically updated from the values in the first_name and last_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 called products 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 the products table, the net_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 the products 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 *;