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
One benefit may consist of this feature; for the small calculation, we don't need to write a trigger or something.
ReplyDeleteyes, you are right
Delete