ALTER TABLE

ALTER TABLE语句用于修改表,其中对分区父表的修改会自动应用到分区子表中。本文为您介绍ALTER TABLE的用法。

使用限制

Hologres当前对修改表的支持情况如下:

  • 目前支持对表进行重命名、增加列和修改表数据生存时间的操作。

  • 支持修改字段的默认值、dictionary_encoding_columns和bitmap_columns属性。

  • 目前不支持修改数据类型。

注意事项

进行修改dictionary_encoding_columns、bitmap_columns、time_to_live_in_seconds等表属性操作时,可能会触发后台异步执行Compaction,占用一定的CPU资源,同时实例的存储量可能会出现先上升后回落的情况。

修改数据类型

Hologres V3.0版本起,支持修改内表列的数据类型。

  • 使用限制

    • 支持修改非分区表、分区父表的列类型,不支持修改分区子表的列类型。

    • 不支持修改分区父表的分区列类型。

    • 不支持COLLATE语法和USING语法。

    • 仅支持如下数据类型的转换:

      源数据类型

      目标类型

      备注

      VARCHAR(N)

      VARCHAR(M)

      要求M>N

      VARCHAR(N)

      TEXT

      CHAR(N)

      CHAR(M)

      要求M>N

      CHAR(N)

      VARCHAR(M)

      要求M>=N

      CHAR(N)

      TEXT

      JSON

      TEXT

      VARCHAR(N)[]

      VARCHAR(M)[]

      要求M>N

      VARCHAR(N)[]

      TEXT[]

  • 使用语法

    ALTER TABLE [ IF EXISTS ] <schema_name>.<table_name> ALTER [ COLUMN ] <column_name> TYPE <data_type>;
  • 使用示例

    DROP TABLE IF EXISTS t;
    
    CREATE TABLE IF NOT EXISTS t (
        a varchar(5)
    );
    
    INSERT INTO t VALUES ('holo'), ('gres');
    
    ALTER TABLE t ALTER COLUMN a TYPE text;

重命名

ALTER TABLE语句可以对表进行重命名,如果目标表不存在,或者重命名目标表为已存在的表名称,系统均会返回异常。

说明

目前不支持跨Schema对表进行重命名操作。

  • 使用语法

    --内部表重命名
    ALTER TABLE [schema_name.]<table_name> RENAME TO <new_table_name>;
    
    --外部表重命名
    ALTER FOREIGN TABLE [schema_name.]<foreign_table_name> RENAME TO <new_foreign_table_name>;
  • 使用示例

    --将表holo_test重命名为holo_test_1
    ALTER TABLE public.holo_test RENAME TO holo_test_1 ;
    
    --将外部表foreign_holo_test重命名为foreign_holo_test_1
    ALTER FOREIGN TABLE public.foreign_holo_test RENAME TO foreign_holo_test_1;

增加列

ALTER TABLE语句可以给表增加列,仅支持在表的最后一列之后增加新的列。

  • 使用语法

    --新增一列
    ALTER TABLE IF EXISTS [schema_name.]<table_name> ADD COLUMN <new_column> <data_type>;
    
    --新增多列
    ALTER TABLE IF EXISTS [schema_name.]<table_name> ADD COLUMN <new_column_1> <data_type>, ADD COLUMN <new_column_2> <data_type>; 
  • 使用示例

    --在表holo_test中增加id列
    ALTER TABLE IF EXISTS public.holo_test ADD COLUMN id int;

删除列(Beta)

Hologres从V2.0版本开始,支持删除列,具体语法如下。

  • 使用限制

    • 仅适用于Hologres V2.0及以上版本,如果您的实例是V2.0以下版本,请您使用自助升级或加入Hologres钉钉交流群反馈,详情请参见如何获取更多的在线支持?

    • 若您的表是分区表,仅能删除分区父表的列,无法直接删除分区子表的列。对于分区子表,在父表的列删除后自动删除。此操作开销较高,建议在业务低峰期执行。

    • 仅只有表Owner才能删除列,若您的数据库使用的是简单权限模型,需要设置为developer用户组权限。

    • 设置了Primary Key、Distribution Key、Clustering Key、Event_time_column属性的列无法删除。

    • 不支持删除外部表的列。

    • 删除JSONB相关列后,相关JSONB索引会一并删除。

    • 删除proxima_vector列时,需要指定cascade参数。

    • 删除Serial列时,如果Sequence是基于这一列创建的,则Sequence会一并被删除。

    • 如果表创建了物化视图,不支持删除源表,也不支持删除源表中被物化视图引用的列。

  • 使用语法

    重要

    Hologres V2.0以下版本不支持删除列。

    set hg_experimental_enable_drop_column = on; --通过该GUC打开功能
    ALTER TABLE IF EXISTS <table_name> DROP COLUMN  [ IF EXISTS ] <column> [ RESTRICT | CASCADE ]
  • 使用示例

    --建表
    begin;
    CREATE TABLE tbl (
     "id" bigint NOT NULL,
     "name" text NOT NULL,
     "age" bigint,
     "class" text NOT NULL,
     "reg_timestamp" timestamptz NOT NULL,
    PRIMARY KEY (id,age)
    );
    call set_table_property('tbl', 'orientation', 'column');
    call set_table_property('tbl', 'distribution_key', 'id');
    call set_table_property('tbl', 'clustering_key', 'age');
    call set_table_property('tbl', 'event_time_column', 'reg_timestamp');
    call set_table_property('tbl', 'bitmap_columns', 'name,class');
    call set_table_property('tbl', 'dictionary_encoding_columns', 'class:auto');
    commit;
    
    --删除指定列
    set hg_experimental_enable_drop_column = on;--Beta阶段,需要通过GUC参数打开这个功能
    ALTER TABLE IF EXISTS tbl DROP COLUMN name;

    查询表:

    SELECT*FROMtbl;
    
    --返回结果
    id    age    class    reg_timestamp
    ----+-----+---------+--------------
                        

重命名列

Hologres从V1.1版本开始,支持重命名列,具体语法如下。

说明
  • 如果您的实例是V1.1以下版本,请您使用自助升级或加入Hologres钉钉交流群反馈,详情请参见如何获取更多的在线支持?

  • 若您的表是分区表,由于存在分区子表和父表数据结构一致性的要求,仅支持重命名分区父表的列,不支持单独重命名某个分区子表的列。重命名分区父表的列,所有子表自动生效。

  • 不支持同时重命名多个表的列名称。

  • 仅只有表Owner才能重命名列,若您的数据库使用的是简单权限模型,需要设置为developer用户组权限。

  • 使用语法

    ALTER TABLE [schema_name.]<table_name> RENAME COLUMN <old_column_name> TO <new_column_name>;
  • 使用示例

    --将表holo_test的id列重命名为name
    ALTER TABLE public.holo_test RENAME COLUMN id TO name; 

修改默认值

ALTER TABLE语句支持修改默认值设置(常量或常量表达式),该默认值仅对设置之后新写入/更新数据有效,不会更新表中已有数据的默认值。当前仅Hologres V0.9.23及以上版本支持修改默认值。具体修改方式说明如下:

  • 使用语法

    --修改表字段的默认值
    ALTER TABLE [schema_name.]<table_name> ALTER COLUMN <column> SET DEFAULT <expression>;
    
    --删除表字段的默认值
    ALTER TABLE [schema_name.]<table_name> ALTER COLUMN <column> DROP DEFAULT;
  • 使用示例

    --修改表holo_test中id列的默认值为0
    ALTER TABLE holo_test ALTER COLUMN id SET DEFAULT 0;
    
    --删除表holo_test中id列的默认值
    ALTER TABLE holo_test ALTER COLUMN id DROP DEFAULT;

修改表属性

Hologres支持通过执行语句修改参数,达到修改表属性的目的。具体修改方式说明如下:

  • 修改dictionary_encoding_columns字典编码列。修改Dictionary Encoding设置,会引起数据文件重新编码存储,会在一段时间内消耗一部分CPU和内存资源,建议在业务低峰期执行变更。

    • 使用语法

      -- 修改dictionary_encoding_columns(2.1版本起)
      ALTER TABLE <schema_name>.<table_name> SET (dictionary_encoding_columns = '[columnName{:[on|off|auto]}[,...]]'); --只支持全量修改
      
      -- 修改dictionary_encoding_columns(所有版本)
      --修改全量
      CALL SET_TABLE_PROPERTY('[schema_name.]<table_name>', 'dictionary_encoding_columns', '[columnName{:[on|off|auto]}[,...]]');
      
      --修改增量,只修改call里面的指定字段,其余字段不变
      CALL UPDATE_TABLE_PROPERTY('[schema_name.]<table_name>', 'dictionary_encoding_columns', '[columnName{:[on|off|auto]}[,...]]');
      重要

      Hologres V2.0版本起,针对UPDATE_TABLE_PROPERTY语法进行了优化,运行如下语句时,表的dictionary_encoding_columns属性保持不变。Hologres V2.0以前版本则是会清空表的dictionary_encoding_columns属性。

      CALL UPDATE_TABLE_PROPERTY('<table_name>','dictionary_encoding_columns','');
    • 参数说明

      参数

      说明

      table_name

      需要和待修改的表名大小写保持一致,可以携带Schema信息。

      on

      表示当前字段打开dictionary_encoding_columns

      off

      表示当前字段关闭dictionary_encoding_columns

      auto

      表示自动。如果是设置了auto,Hologres会根据所在列数值的重复程度自动选择是否进行dictionary_encoding_columns,值的重复度越高,字典编码的收益越大。在Hologres V0.8版本及更早版本中默认所有text列都会被设置为dictionary_encoding_columns,在Hologres V0.9版本及之后版本,会根据数据特征自动选择是否创建字典编码。

    • 使用示例

      • 对a列显示创建dictionary,b列自动选择是否创建dictionary,c、d两列不创建dictionary。

        CREATE TABLE dwd.holo_test (
         a text NOT NULL,
         b text NOT NULL,
         c text NOT NULL,
         d text
        );
        CALL UPDATE_TABLE_PROPERTY('dwd.holo_test','dictionary_encoding_columns','a:on,b:auto');
      • 对a列显式关闭dictionary,系统也会自动给b、c、d字段加上dictionary索引。

        CREATE TABLE dwd.holo_test (
         a text NOT NULL,
         b text NOT NULL,
         c text NOT NULL,
         d text
        );
        CALL SET_TABLE_PROPERTY('dwd.holo_test','dictionary_encoding_columns','a:off');
  • 修改bitmap_columns比特编码列

    Hologres从V0.9版本开始支持通过执行以下语句修改bitmap_columns,无需再重新建表即可修改表属性。

    • 使用语法

      -- 修改bitmap_columns(2.1版本起)
      ALTER TABLE <schema_name>.<table_name> SET (bitmap_columns = '[columnName{:[on|off]}[,...]]');
      
      -- 修改bitmap_columns(所有版本)
      --修改全量
      CALL SET_TABLE_PROPERTY('[schema_name.]<table_name>', 'bitmap_columns', '[columnName{:[on|off]}[,...]]');
      
      --修改增量,只修改call里面的指定字段,其余字段不变
      CALL UPDATE_TABLE_PROPERTY('[schema_name.]<table_name>', 'bitmap_columns', '[columnName{:[on|off]}[,...]]');
      重要

      Hologres V2.0版本起,针对UPDATE_TABLE_PROPERTY语法进行了优化,运行如下语句时,表的bitmap_columns属性保持不变。Hologres V2.0以前版本则是会清空表的bitmap_columns属性。

      CALL UPDATE_TABLE_PROPERTY('<table_name>','bitmap_columns','');
    • 参数说明

      参数

      说明

      table_name

      需要和待修改的表名大小写保持一致,可以携带Schema信息。

      on

      当前字段打开bitmap_columns

      off

      当前字段关闭bitmap_columns

    • 使用示例

      • 对a列启动bitmap索引,对b、c、d不启动bitmap索引。

        CREATE TABLE dwd.holo_test (
         a text NOT NULL,
         b text NOT NULL,
         c text NOT NULL,
         d text
        );
        CALL UPDATE_TABLE_PROPERTY('dwd.holo_test','bitmap_columns','a:on');
      • 对b关闭bitmap索引,系统会自动给a、c、d创建bitmap索引。

        CREATE TABLE dwd.holo_test_1 (
         a text NOT NULL,
         b text NOT NULL,
         c text NOT NULL,
         d text
        );
        CALL SET_TABLE_PROPERTY('dwd.holo_test_1','bitmap_columns','b:off');
  • 修改表数据的生存时间

    • 使用语法

      call set_table_property('[schema_name.]<table_name>', 'time_to_live_in_seconds', '<non_negative_literal>');
    • 参数说明

      参数

      说明

      time_to_live_in_seconds

      简称TTL,表数据的生存时间,单位为秒,必须是正整数。

      说明

      表数据生存时间是按照数据写入Hologres开始,超过该指定时间,表数据将会在某个时间内被删除,但并不是精准的时间。

    • 使用示例

      call set_table_property('dwd.holo_test', 'time_to_live_in_seconds', '600');

修改表所在Schema

Hologres从 V1.3版本开始,支持修改表所在的Schema,例如将表从schema1移动至schema2,无须重新建表导数据,实现快速表路径切换。

  • 使用语法

    ALTER TABLE [ IF EXISTS ] [<schema>.]<table_name>
        SET SCHEMA <new_schema>;

    schema为表所在的Schema名称;table_name为修改表的名称;new_schema为移动至新Schema的名称。

  • 使用示例

    将表名称为tb1的表从publicSchema移动至testschemaSchema下。

    ALTER TABLE IF EXISTS public.tbl
        SET SCHEMA testschema;

HoloWeb可视化修改表

HoloWeb提供可视化编辑表功能,无需写SQL命令就能修改表字段和部分表属性,步骤如下。

  1. 进入HoloWeb页面,详情请参见连接HoloWeb并执行查询

  2. HoloWeb页面顶部菜单栏,单击元数据管理

  3. 元数据管理页面左侧的已登录实例列表,双击要修改的目标表。

  4. 在表的详情页面,可视化修改表的字段和部分表属性。

    44345

  5. 单击右上角的提交,完成表修改。