PostgreSQL – Generate Columns

Generated Columns is a special column that is computed with the help of other columns. Generated Column is updated whenever the changes occur in a row and cannot be overridden. In Generated Columns result calculated by refer to the other column of the table. The Generation Column can only use immutable functions and cannot use subqueries.

Generated Columns can be of two types either Stored or Virtual. Stored Generated Columns evaluated when it is written like in basic command (INSERT or UPDATED), it occupies storage and similar to the materialized view (except that always updated automatically). Virtual Generated Columns evaluated when it is read, it occupies no storage and similar to the view.

But currently, PostgreSQL implements only stored generated columns.

Syntax of PostgreSQL for CREATE TABLE using Generated Columns as follows:

CREATE TABLE table_name (
  column_name_1 datatype(length)  column constrain (if any),
  column_name_2 datatype(length)  column constrain (if any) ,
   .
   .
   .
   .
  column_name datatype GENERATED ALWAYS AS  (expression) STORED
);

 A keyword STORED must be specified as which type of column.

 Example: A compute expression by Generated Columns:

 CREATE TABLE  Addition (
    number_1 int,
   number_2 int,
   number_3 int,
   add int  GENERATED ALWAYS AS (number_1 + number_2 + number_3) STORED
)
 INSERT INTO  Addition(number_1, number_2, number_3) VALUES  (1,2,3) , (8,-9,2) , (7,1 ,NULL);
 TABLE Addition; 

Output: