How to Create GENERATED ALWAYS AS Column in PostgreSQL

In PostgreSQL, you create a GENERATED ALWAYS AS column by defining it as a computed column using the syntax GENERATED ALWAYS AS (expression) STORED. This column automatically calculates its value from other columns and cannot be directly inserted or updated.

Syntax

The syntax to create a generated always as column in PostgreSQL is:

• column_name: the name of the generated column.
• data_type: the data type of the generated column.
• GENERATED ALWAYS AS (expression): defines the column as computed from the given expression.
• STORED: means the value is physically stored and updated automatically.

sql

column_name data_type GENERATED ALWAYS AS (expression) STORED

Example

This example creates a table with two columns price and quantity, and a generated column total that multiplies them. The total column is automatically calculated and stored.

sql

CREATE TABLE sales (
  price numeric(10,2),
  quantity integer,
  total numeric(12,2) GENERATED ALWAYS AS (price * quantity) STORED
);

INSERT INTO sales (price, quantity) VALUES (10.00, 3), (5.50, 4);

SELECT * FROM sales;

Output

 price | quantity | total 
-------+----------+-------
 10.00 |        3 | 30.00
  5.50 |        4 | 22.00
(2 rows)

Common Pitfalls

Common mistakes when creating generated columns include:

• Forgetting the STORED keyword, which is required in PostgreSQL.
• Trying to insert or update the generated column directly, which is not allowed.
• Using volatile functions or non-deterministic expressions in the generated column.

Example of wrong and right usage:

sql

-- Wrong: missing STORED keyword
CREATE TABLE wrong_example (
  a int,
  b int GENERATED ALWAYS AS (a + 1)
);

-- Right:
CREATE TABLE right_example (
  a int,
  b int GENERATED ALWAYS AS (a + 1) STORED
);

Quick Reference

Keyword	Description
GENERATED ALWAYS AS (expression)	Defines the column as computed from the expression
STORED	Stores the computed value physically in the table
Cannot insert/update	Generated columns cannot be directly modified
Expression restrictions	Expression must be immutable and deterministic

Key Takeaways

Use GENERATED ALWAYS AS (expression) STORED to create computed columns in PostgreSQL.

Generated columns automatically calculate values and cannot be directly inserted or updated.

Always include the STORED keyword; it is mandatory in PostgreSQL.

Expressions must be deterministic and cannot use volatile functions.

Check for common mistakes like missing STORED or trying to modify generated columns.