Generated column-AnalyticDB(AnalyticDB)-阿里云帮助中心

AnalyticDB for PostgreSQL 7.0 supports generated columns. A generated column is a special column computed from other columns in the same table. You cannot directly write data to a generated column using INSERT or UPDATE. When you write data to the source columns, the database automatically evaluates the expression and stores the result in the generated column.

Syntax

GENERATED ALWAYS AS ( generation_expr ) STORED

Parameters

Parameter	Description
generation_expr	The expression for computing the column's value. Note You can use only immutable functions and operators. The expression can reference only non-generated columns within the same table.
STORED	Specifies that the computed values are physically stored on disk. Currently, only STORED is supported.

Example

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

Check the schema of the t1 table.

Run the \d t1 command in psql to view the table schema:

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)

Insert data into the t1 table.

INSERT INTO t1(stuid,chinese,math) VALUES(1, 90, 95);

Query the data in the t1 table.

SELECT * FROM t1;

Output:

stuid | chinese | math | sum_score
-------+---------+------+-----------
     1 |      90 |   95 |       185
(1 row)

Attempt to write data directly to the generated column.

INSERT INTO t1 (stuid, chinese, math, sum_score) VALUES(1,80,70,100);

The command returns an error, confirming that you cannot write data directly to a generated column.

ERROR:  cannot insert into column "sum_score"
DETAIL:  Column "sum_score" is a generated column

Create an index on the generated column.

CREATE INDEX ON t1 USING BTREE(sum_score);

Check the schema of the t1 table.

Run the \d t1 command in psql again. The output confirms the index was 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)