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
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.
- 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.