PostgreSQL 12 introduced a useful feature called generated columns, which allows you to define columns that automatically calculate their values based on other columns in the same table. This makes your data model more robust, expressive, and helps reduce redundancy in queries and application logic.
What Are Generated Columns?
A generated column is a column whose value is automatically computed from other columns in the same row. You don’t insert or update this column directly—it updates itself.
PostgreSQL currently supports only stored generated columns, which are computed once on insert or update and stored on disk (as opposed to virtual columns which are calculated at read time).
Syntax
column_name data_type GENERATED ALWAYS AS (expression) STORED
* column_name: Name of the generated column
* data_type: Data type of the column (must match the result of the expression)
* expression: An expression that uses other columns in the same row
* STORED: Indicates that the column is stored (required)
NOTE: You cannot modify a generated column directly via INSERT or UPDATE.
Example 1: Full Name Column
Let’s say you have a users table with first_name and last_name. You want to store the full name as a computed column.
CREATE TABLE users (
id SERIAL PRIMARY KEY,
first_name TEXT,
last_name TEXT,
full_name TEXT GENERATED ALWAYS AS (first_name || ' ' || last_name) STORED
);
Insert Data
INSERT INTO users (first_name, last_name) VALUES ('John', 'Doe');Select
SELECT * FROM users;
id | first_name | full_name | last_name
----+------------+-----------+-----------
1 | John | John Doe | Doe
(1 row)
Example 2: Price With Tax
Imagine a products table with price and tax_rate, and you want to store price_with_tax.
CREATE TABLE products (
id SERIAL PRIMARY KEY,
name TEXT,
price NUMERIC,
tax_rate NUMERIC,
price_with_tax NUMERIC GENERATED ALWAYS AS (price + (price * tax_rate)) STORED
);
Insert Data
INSERT INTO products (name, price, tax_rate)
VALUES ('Laptop', 1000, 0.18);
Select
SELECT name, price_with_tax FROM products;
name | price_with_tax
--------+----------------
Laptop | 1180.00
(1 row)
Limitations
1. Only stored generated columns are supported in PostgreSQL (no virtual/computed-at-read-time columns).
2. You cannot insert or update generated columns manually.
3. The expression used must be immutable (no functions like now() or random()).
4. Cannot use subqueries, user-defined functions with side effects, etc.
Use Cases
* Derived values (e.g., full_name, price_with_tax)
* Normalized design while still providing denormalized convenience
* Indexes on expressions via generated columns
* Simplify reporting and analytics.
Indexing on Generated Columns
You can create indexes on generated columns, just like regular columns.
CREATE INDEX idx_products_price_with_tax ON products(price_with_tax);
This helps speed up queries filtering or sorting on those derived values.
Generated columns in PostgreSQL help reduce redundant logic in your application by letting the database maintain derived values for you. They're great for simplifying query patterns, improving data consistency, and optimizing performance for certain derived expressions.
If you're using PostgreSQL 12 or later, this is a feature worth exploring in your schema design.