AnalyticDB:Generated column

How it works

Generated columns in AnalyticDB for PostgreSQL are always stored: the computed value is written to disk when a row is inserted or updated, just like a regular column. This makes generated columns suitable for indexing and query acceleration.

Syntax

GENERATED ALWAYS AS ( generation_expr ) STORED

Parameters

Limitations

• The generation expression may only use immutable functions and operators.

• The generation expression must reference only non-generated columns in the current table.

• STORED must be specified.

• You cannot write to a generated column directly with INSERT or UPDATE.

Example

The following example creates a table with a generated column that stores the sum of two score columns, then demonstrates inserting data, querying, and creating an index on the generated column.

1. Create the table.

CREATE TABLE t1 (
    stuid     integer,
    chinese   smallint,
    math      smallint,
    sum_score smallint GENERATED ALWAYS AS (chinese + math) STORED
) DISTRIBUTED BY (stuid);

2. Check the table schema.

Run \d t1 in psql. The output confirms that sum_score is a 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
Distributed by: (stuid)

3. Insert a row.

Write values only to the source columns. The sum_score value is computed automatically.

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

4. Query the table.

SELECT * FROM t1;

Output:

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

5. Attempt a direct write to the generated column.

Writing directly to sum_score returns an error:

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

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

6. Create a B-tree index on the generated column.

CREATE INDEX ON t1 USING BTREE (sum_score);

7. Verify the index.

Run \d t1 again to confirm the index was created:

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)