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

使用限制

Hologres当前对修改表的支持情况如下:
  • 目前支持对表进行重命名、增加列和修改表数据生存时间的操作。
  • 支持修改字段的默认值、dictionary_encoding_columns和bitmap_columns属性。
  • 目前不支持修改数据类型。

注意事项

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

重命名

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;

重命名列

Hologres从V1.1版本开始,支持重命名列,具体语法如下。
说明
  • 如果您的实例是V1.1以下版本,请您提交工单或加入在线支持钉钉群申请升级实例。
  • 若您的表是分区表,由于存在分区子表和父表数据结构一致性的要求,仅支持重命名分区父表的列,不支持单独重命名某个分区子表的列。重命名分区父表的列,所有子表自动生效。
  • 不支持同时重命名多个表的列名称。
  • 仅只有表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和内存资源,建议在业务低峰期执行变更。
    • 使用语法
      --修改全量
      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]}[,...]]');
    • 参数说明
      参数 说明
      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,无需再重新建表即可修改表属性。

    • 使用语法
      --修改全量
      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]}[,...]]');
    • 参数说明
      参数 说明
      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. 在表的详情页面,可视化修改表的字段和部分表属性。修改表
  5. 单击右上角的提交表,完成表修改。