PostgreSQL 12 introduced a new feature called

. Other popular databases already support to

as the

and

.

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 );





