生成列(Beta)

生成列(Generated Columns)是由其他列计算得到的特殊的列,分为存储生成列(Stored Generated Columns)和虚拟生成列(Virtual Generated Columns)。从Hologres V3.1版本开始,支持存储生成列,即在数据写入或更新时自动进行计算,并实际占用存储空间。目前暂不支持虚拟生成列。本文为您介绍Hologres中存储生成列的使用方法。

使用场景

  • 实现对所需字段的自动计算:消除手动处理计算逻辑的业务需求。

  • 保障数据的一致性:避免人为操作或代码逻辑的错误,而导致数据不一致。

  • 优化查询性能:对于高频查询的场景,存储生成列的读取等同于普通列。

  • 简化业务逻辑:对于固定常用数据转换操作,能够降低SQL复杂度。

根据您的业务需要,合理使用生成列能显著提升开发效率并保障数据的可靠性。

语法

通过GENERATED ALWAYS AS子句声明生成列,并通过STORED关键字来指定存储生成列。

  • 创建包含生成列的表。

    CREATE TABLE generated_col_t (
        [...,]
        col1 INT,
        col2 INT GENERATED ALWAYS AS (col1 + 1) STORED
    );
  • 创建包含生成列的逻辑分区表,并将生成列作为分区键。

    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

    • 定义生成列时,只支持使用IMMUTABLE的函数或表达式,不支持非IMMUTABLE函数,如CURRENT_DATE、RANDOM等。

    • 定义生成列时,表达式不支持引用其他生成列,不支持对生成列定义default关键字。

    • 创建分区表时,支持将逻辑分区表的分区键设为生成列,不支持将物理分区表的分区键设为生成列。而分区表中的普通列均支持设为生成列。

    • 创建外部表(CREATE FOREIGN TABLE)时,不支持定义生成列。

    • 支持将生成列设为Hologres各类索引,包括主键、分布键、分段键、聚簇索引、位图索引和字典编码列。

  • ALTER TABLE

    • 不支持ADDColumn为生成列。

    • 支持DROP生成列。但在生成列未被DROP之前,不支持DROP该生成列的引用列。

    • 不支持修改生成列的数据类型,不支持修改生成列引用列的数据类型。建议您通过REBUILD功能实现,详情请参见REBUILD(Beta)

    • 支持RENAME生成列列名。

  • DML/DQL

    • 数据写入或更新含生成列的表时,允许不指定生成列或使用default关键字,但不支持指定生成列直接写入。

    • 数据更新时,如果生成列或其引用的列是分布键(distribution_key),则不支持更新该列。

    • 通过Fixed Plan进行数据更新时,如果主键为生成列,则不支持更新生成列的引用列。

    • 通过Fixed Plan进行部分列更新时,如果生成的列引用多个普通列,则不支持只更新其中的部分列。

    • 其他方式的操作均支持包含生成列的表,包括通过HQE引擎执行的读写、通过Fixed Plan执行的读写以及Copy等方式。

  • 其他操作

    • CREATE TABLE LIKE创建时,支持原表有生成列,需开启hg_experimental_enable_create_table_like_properties参数,以保留生成列属性。

    • CREATE TABLE AS创建时,不支持原表有生成列。

    • 如需修改包含生成列的表的参数,支持通过REBUILD语法(含迁移表的Table Group),请参见REBUILD(Beta)。不支持使用HG_MOVE_TABLE_TO_TABLE_GROUP语法迁移表的Table Group。

    • 如需对包含生成列的表执行INSERT OVERWRITE操作,Hologres V3.1版本支持原生INSERT OVERWRITE语法。不支持原hg_insert_overwrite语法,详情请参见该语法的INSERT OVERWRITE

使用示例

  1. 新建带有生成列的表。

    CREATE TABLE generated_col_t (
        id INT PRIMARY KEY,
        col1 INT,
        col2 INT GENERATED ALWAYS AS (col1 + 1) STORED
    );
  2. 数据导入。

    • 支持数据导入全部非生成列。示例如下:

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

      通过查询命令SELECT * FROM generated_col_t;显示结果如下。

      id	col1	col2
      1	1	2
      2	2	3
    • 支持数据导入时对生成列使用default关键字。示例如下:

      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;显示结果如下。

      id	col1	col2
      4	4	5
      2	2	3
      3	3	4
      1	1	2
    • 不支持对指定生成列进行导入。示例如下:

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

      返回结果如下。

      a1 - 副本

  3. 数据更新。

    • 支持更新非生成列数据。示例如下:

      UPDATE generated_col_t SET col1 = 2 WHERE id = 1;

      通过查询命令SELECT * FROM generated_col_t;显示结果如下。

      id	col1	col2
      2	2	3
      3	3	4
      4	4	5
      1	2	3     -- 该列数据已发生变化 
    • 支持更新数据时对生成列使用default关键字。示例如下:

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

      通过查询命令SELECT * FROM generated_col_t;显示结果如下。

      id	col1	col2
      3	3	4
      2	3	4    -- 该列数据已发生变化
      4	4	5
      1	2	3
    • 不支持对指定生成列进行更新。示例如下:

      UPDATE generated_col_t SET col2 = 4 WHERE id = 3;

      返回结果如下。

      777

可通过以下SQL查询函数在特定参数类型下是否为IMMUTABLE。例如:TO_CHAR函数仅在输入为TIMESTAMP WITH TIME ZONE类型时为IMMUTABLE,因此在生成列中使用该函数时,必须确保参数类型匹配。

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
-- 目标函数
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;