AnalyticDB for PostgreSQL V7.0 supports the generated column feature. You can define a generated column based on the value of another column. Data cannot be directly written to the generated column by using INSERT or UPDATE. When data is written to one or more original columns, the automatic calculation result of an expression is written to the generated column.

Syntax

GENERATED ALWAYS AS ( generation_expr ) STORED

Parameters

Parameter Description
generation_expr The original column to be referenced.
Note
  • The original column can contain only functions and operations of immutable types.
  • The original column must be a non-generated column in the current table.
STORED Specifies to store the data of the generated column in disks. STORED must be specified.

Example

  1. Create a test table named t1. 
    CREATE TABLE t1 (stuid int4, chinese int2, math int2, sum_score int2 GENERATED ALWAYS AS (chinese+math) STORED ) distributed by (stuid);
  2. Query the schema of the t1 table.
    Run the \d t1 command on psql to query the table schema. The following information is returned:
    Table "public.t1"
  Column   |   Type   | Collation | Nullable |                   Default
-----------+----------+-----------+----------+---------------------------------------------
 stuid     | integer  |           |          |
 chinese   | smallint |           |          |
 math      | smallint |           |          |
 sum_score | smallint |           |          | generated always as (chinese + math) stored
Distributed by: (stuid)
  3. Insert data to the t1 table. 
    INSERT INTO t1(stuid,chinese,math) VALUES(1, 90, 95);
  4. Query data in the t1 table. 
    SELECT * FROM t1;
    The following information is returned:
    stuid | chinese | math | sum_score
-------+---------+------+-----------
     1 |      90 |   95 |       185
(1 row)
  5. Write data directly to the generated column. 
    INSERT INTO t1 (stuid, chinese, math, sum_score) VALUES(1,80,70,100);
    The following error message is returned. Data cannot be directly written to the generated column. 
    ERROR:  cannot insert into column "sum_score"
DETAIL:  Column "sum_score" is a generated column
  6. Create an index on the generated column. 
    CREATE INDEX ON t1 USING BTREE(sum_score);
  7. Query the schema of the t1 table.
    Run the \d t1 command on psql to query the table schema. The following information is returned. An index is created on the generated column. 
     Table "public.t1"
  Column   |   Type   | Collation | Nullable |                   Default
-----------+----------+-----------+----------+---------------------------------------------
 stuid     | integer  |           |          |
 chinese   | smallint |           |          |
 math      | smallint |           |          |
 sum_score | smallint |           |          | generated always as (chinese + math) stored
Indexes:
    "t1_sum_score_idx" btree (sum_score)
Distributed by: (stuid)