PostgreSQL Generated Always: Generate Computed Columns

A generated column is a unique column that is always computed from other existing columns. You cannot insert data into generated column but it returns computed data. It's similar to what a view is for tables.

Use the GENERATED ALWAYS clause with the column definition in the CREATE TABLE or ALTER TABLE statement to create the generated columns.

<column_name> <datatype> GENERATED ALWAYS AS(expression) STORED

In the GENERATED ALWAYS AS (expression) Stored clause, specify an expression using existing column names to generate the calculated values for the generated column.

There are two kinds of generated columns: stored and virtual. A stored generated column stores the computed values the same as a normal column. A virtual generated column does not store any values and returns computed values when it is read. PostgreSQL v14 currently implements only stored generated columns using the Stored keyword.

The following example demonstrates the generated column.

CREATE TABLE cars(
    car_id INT ,
    brand_name VARCHAR NOT NULL,
	priceInDollar numeric,
	princeInPound numeric GENERATED ALWAYS AS (priceInDollar * 0.22) STORED
)

In the above create table statement, the priceinPound column is declared as a generated column with the expression priceInDollar * 0.22. The stored keyword makes it a stored generated column. You must use the Stored keyword in PostgreSQL v14.

You can now insert data into the CARS table using the INSERT statement.

INSERT INTO CARS VALUES(1, 'Honda', 25000);

Now, read the data using the SELECT statement.

As you can see, priceInPound column returns the calculated value.

Trying to insert data into a generated column will raise an error, as shown below.

Important Points:

• The generation expression can only use immutable functions and cannot use subqueries or reference anything other than the current row in any way.
• A generation expression cannot reference another generated column.
• A generation expression cannot reference a system column, except tableoid.
• A generated column cannot have a column default or an identity definition.
• A generated column cannot be part of a partition key.
• Foreign tables can have generated columns

Learn more about generated columns in detail.