PostgreSQL Generated Columns

What is a generated column

In PostgreSQL, a generated column is a special column whose value is automatically calculated based on the expression in the column definition. You cannot directly write or update the value of the generated column. Generated columns are constraints defined on columns.

There are two types of generated columns:

• Virtual Generated Columns: Column values ​​are not stored. When reading this column, the value of this column is calculated.
• Store Generated Columns: Column values ​​are stored. When inserting or modifying the row, the value of this column is recalculated and stored on disk.

PostgreSQL currently only supports stored generated columns.

Syntax for Generated Columns

To create a generated column, use the following syntax:

col_name data_type
  GENERATED ALWAYS AS (expr) STORED

Note the second line GENERATED ALWAYS AS (expr) STORED:

• The GENERATED ALWAYS AS keyword indicates that this column is a generated column.
• The expr is the expression used to generate the value for this column.
• The STORED keyword indicates that this column is a stored generated column. This is the only generated column type currently supported by PostgreSQL.

You can define a generated column when creating a table with the CREATE TABLE statement, or add a generated column with the ALTER TABLE statement.

PostgreSQL generated column Examples

Suppose you have an order items table that stores the price and quantity of items in every order. Create the order_item table using the following statement:

CREATE TABLE order_item (
  order_item_id SERIAL PRIMARY KEY,
  goods VARCHAR(45) NOT NULL,
  price DECIMAL NOT NULL,
  quantity INTEGER NOT NULL
);

Insert 2 rows of test data using the following statement:

INSERT INTO order_item (goods, price, quantity)
VALUES ('Apple', 5, 3), ('Peach', 4, 4);

Find all rows of the order_item table using the following statement:

SELECT * FROM order_item;

order_item_id | goods | price | quantity
---------------+-------+-------+----------
            1 | Apple |     5 |        3
            2 | Peach |     4 |        4
(2 rows)

Suppose, you need to find the total amount of each order item, use the following SQL statement:

SELECT
  goods,
  price,
  quantity,
  (price * quantity) AS total_amount
FROM order_item;

goods | price | quantity | total_amount
-------+-------+----------+--------------
Apple |     5 |        3 |           15
Peach |     4 |        4 |           16
(2 rows)

The above statement uses price * quantity to calculate the total amount and uses total_amount as the column alias.

There is no doubt that this is correct.

PostgreSQL generated columns can simplify our work and save you from writing such complex SELECT statements. Now we want to add a generated column with the following statement:

ALTER TABLE order_item
  ADD COLUMN total_amount DECIMAL
    GENERATED ALWAYS AS (price * quantity) STORED;

Here, we’ve added a total_amount column. It is a generated column and its computed expression is price * quantity.

Use the \d command to see the structure of order_item:

\d order_item

                                              Table "public.order_item"
   Column     |         Type          | Collation | Nullable |                        Default
---------------+-----------------------+-----------+----------+--------------------------------------------------------
order_item_id | integer               |           | not null | nextval('order_item_order_item_id_seq'::regclass)
goods         | character varying(45) |           | not null |
price         | numeric               |           | not null |
quantity      | integer               |           | not null |
total_amount  | numeric               |           |          | generated always as (price * quantity::numeric) stored
Indexes:
   "order_item_pkey" PRIMARY KEY, btree (order_item_id)

You can find from the above output that total_amount is a storage generated column.

Query all rows in the table with the following statement:

SELECT
  goods,
  price,
  quantity,
  total_amount
FROM order_item;

goods | price | quantity | total_amount
-------+-------+----------+--------------
Apple |     5 |        3 |           15
Peach |     4 |        4 |           16
(2 rows)

Now, you don’t need to write as complex SQL statements as before.

Update generated column

You cannot directly insert or update the value of a generated column. This throws an error. Let’s try it out and see what happens.

Let’s try inserting a row with generated column value ​​first:

INSERT INTO order_item (goods, price, quantity, total_amount)
VALUES ('Banana', 6, 4, 24);

ERROR:  cannot insert into column "total_amount"
DETAIL:  Column "total_amount" is a generated column.

Let’s try again to modify the value of the generated column:

UPDATE order_item
SET total_amount = 30
WHERE goods = 'Apple';

ERROR:  column "total_amount" can only be updated to DEFAULT
DETAIL:  Column "total_amount" is a generated column.

Conclusion

In this article, we learned the usage of PostgreSQL generated columns. Here are the main points of this article:

• A generated column is a column that can be calculated automatically.
• There are two types of generated columns: VIRTUAL and STORED. PostgreSQL currently supports storing generated columns.
• Generated column values ​​cannot be written or modified.