Hologres:Generated columns

Create a table with a generated column

CREATE TABLE generated_col_t (
    col1 INT,
    col2 INT GENERATED ALWAYS AS (col1 + 1) STORED
);

Use a generated column as the partition key

Use a generated column as the partition key of a logical partitioned table. The following example partitions rows by calendar day, derived from a timestamp column:

CREATE TABLE generated_col_logical_part (
    a TEXT,
    b INT,
    ts TIMESTAMP NOT NULL,
    d TIMESTAMP GENERATED ALWAYS AS (date_trunc('day', ts)) STORED NOT NULL
)
LOGICAL PARTITION BY LIST(d);

CREATE TABLE

• Only IMMUTABLE functions and expressions are supported. Non-IMMUTABLE functions such as CURRENT_DATE and RANDOM are not supported.

• A generated column's expression cannot reference another generated column.

• The default keyword cannot be used when defining a generated column.

• The partition key of a logical partitioned table can be a generated column. The partition key of a physical partitioned table cannot be a generated column; however, any regular column in a partitioned table can be a generated column.

• Generated columns cannot be defined on foreign tables created with CREATE FOREIGN TABLE.

• A generated column can serve as any of the following index types: primary key, distribution key, segment key, cluster index, bitmap index, or dictionary-encoded column.

ALTER TABLE

• Adding a generated column to an existing table is not supported (ADD is not supported for generated columns).

• A generated column can be dropped. Before dropping it, retain all columns it references.

• The data type of a generated column or any column it references cannot be modified directly. Use the REBUILD feature instead. For details, see REBUILD.

• A generated column can be renamed.

DML and DQL

• When inserting or updating rows, either omit the generated column or use the default keyword. Directly assigning a value to a generated column is not supported.

• A generated column or any column it references cannot be updated if either is configured as a distribution key.

• If the primary key includes a generated column, the primary key must also include all columns that the generated column references, to support data updates via Fixed Plan.

• When performing a partial column update via Fixed Plan, if a generated column references multiple regular columns, updating only a subset of those referenced columns is not supported.

• The following operations are supported on tables with generated columns: read and write via the HQE engine, read and write via Fixed Plan, and Copy operations.

Other operations

• CREATE TABLE LIKE can copy a table that has generated columns. To retain the generated column properties, enable the hg_experimental_enable_create_table_like_properties parameter.

• CREATE TABLE AS does not support source tables that have generated columns.

• To modify table parameters — including migrating a table to a different table group — use the REBUILD syntax. The HG_MOVE_TABLE_TO_TABLE_GROUP syntax is not supported for tables with generated columns. For details, see REBUILD.

• For INSERT OVERWRITE on a table with a generated column, use the native INSERT OVERWRITE syntax introduced in Hologres V3.1. The hg_insert_overwrite syntax is no longer supported. For details, see INSERT OVERWRITE.

Create a table

CREATE TABLE generated_col_t (
    id INT PRIMARY KEY,
    col1 INT,
    col2 INT GENERATED ALWAYS AS (col1 + 1) STORED
);

Insert data

Omit the generated column, or use default — do not provide a literal value.

Omit the generated column:

INSERT INTO generated_col_t VALUES (1, 1);
INSERT INTO generated_col_t(id, col1) VALUES (2, 2);

SELECT * FROM generated_col_t; returns:

id  col1  col2
1   1     2
2   2     3

Use default for the generated column:

INSERT INTO generated_col_t VALUES (3, 3, default);
INSERT INTO generated_col_t(id, col1, col2) VALUES (4, 4, default);

SELECT * FROM generated_col_t; returns:

id  col1  col2
4   4     5
2   2     3
3   3     4
1   1     2

Providing a literal value returns an error:

INSERT INTO generated_col_t VALUES (5, 5, 6);
INSERT INTO generated_col_t(id, col1, col2) VALUES (6, 6, 7);

Update data

Updating a referenced column recomputes the generated column automatically.

Update a referenced column:

UPDATE generated_col_t SET col1 = 2 WHERE id = 1;

SELECT * FROM generated_col_t; returns:

id  col1  col2
2   2     3
3   3     4
4   4     5
1   2     3     -- col2 recomputed from updated col1

Use default for the generated column in an UPDATE:

UPDATE generated_col_t SET col1 = 3, col2 = default WHERE id = 2;

SELECT * FROM generated_col_t; returns:

id  col1  col2
3   3     4
2   3     4    -- col2 recomputed
4   4     5
1   2     3

Providing a literal value for the generated column returns an error:

UPDATE generated_col_t SET col2 = 4 WHERE id = 3;

Verify that a function is IMMUTABLE

Not all overloads of a function are IMMUTABLE. For example, TO_CHAR is IMMUTABLE only when the input is of the TIMESTAMP WITH TIME ZONE type. Use the following query to check the volatility of a function for specific parameter types before using it in a generated column expression:

SELECT n.nspname AS "Schema",
  p.proname AS "Name",
  pg_catalog.pg_get_function_result(p.oid) AS "Result data type",
  pg_catalog.pg_get_function_arguments(p.oid) AS "Argument data types",
 CASE p.prokind
  WHEN 'a' THEN 'agg'
  WHEN 'w' THEN 'window'
  WHEN 'p' THEN 'proc'
  ELSE 'func'
 END AS "Type",
 CASE
  WHEN p.provolatile = 'i' THEN 'immutable'
  WHEN p.provolatile = 's' THEN 'stable'
  WHEN p.provolatile = 'v' THEN 'volatile'
 END AS "Volatility",
 CASE
  WHEN p.proparallel = 'r' THEN 'restricted'
  WHEN p.proparallel = 's' THEN 'safe'
  WHEN p.proparallel = 'u' THEN 'unsafe'
 END AS "Parallel",
 pg_catalog.pg_get_userbyid(p.proowner) AS "Owner",
 CASE WHEN prosecdef THEN 'definer' ELSE 'invoker' END AS "Security",
 pg_catalog.array_to_string(p.proacl, E'\n') AS "Access privileges",
 l.lanname AS "Language",
 p.prosrc AS "Source code",
 pg_catalog.obj_description(p.oid, 'pg_proc') AS "Description"
FROM pg_catalog.pg_proc p
     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace
     LEFT JOIN pg_catalog.pg_language l ON l.oid = p.prolang
-- Target function
WHERE p.proname OPERATOR(pg_catalog.~) '^(TO_CHAR)$' COLLATE pg_catalog.default
  AND pg_catalog.pg_function_is_visible(p.oid)
ORDER BY 1, 2, 4;