PostgreSQL: Generated Column

PostgreSQL 12 introduced a new feature called generated columns. Other popular databases already support to generated columns as the Computed column and virtual column.

What is a generated column?

A generated column is for columns that are a view on a table. The value of the generated column is always computed or generated from other columns in the table.

Create generated column in PostgreSQL

Using GENERATED ALWAYS AS clause we can create the generated column

PostgreSQL Syntax

CREATE TABLE table_name (     col_1 data_type,     column_2 data_type,           .........           .........           col_3 data_type GENERATED ALWAYS AS (some_calculation) STORED );

table_name is your table name.

col_1, col_1 is the columns used in the calculation to create "generated column."

some_calculation is calculation of col_1 and col_2 like (col_1+col_2)

col_3 is the name of the generated column.

Example of creating Generated Column

I am creating an employee table, here we need to calculate the provident fund distribution of employees, it is fixed and no need to change again and again then here we can use the concept of the generated column. The idea behind the calculation of PF (provident fund) is 12 percent of the basic salary of the employee.

CREATE TABLE employee (        ID  INT GENERATED BY DEFAULT AS IDENTITY,        name text,        basic_salary decimal,        pf_percentage decimal,        emp_pf_distribution decimal GENERATED ALWAYS AS ((basic_salary*pf_percentage)/100) STORED ); INSERT        INTO employee (name, basic_salary, pf_percentage)        VALUES('Dilip', 50000,12);

SELECT * FROM employee;

Result