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;

删除列（Beta）

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

  • 使用限制
    • 仅适用于Hologres V2.0及以上版本，如果您的实例是V2.0以下版本，请您使用自助升级或加入实时数仓Hologres交流群（钉钉群号：32314975）申请升级实例。
    • 若您的表是分区表，仅能删除分区父表的列，无法直接删除分区子表的列。对于分区子表，在父表的列删除后自动删除。此操作开销较高，建议在业务低峰期执行。
    • 仅只有表Owner才能删除列，若您的数据库使用的是简单权限模型，需要设置为developer用户组权限。
    • 设置了Primary Key、Distribution Key、Clustering Key、Event_time_column属性的列无法删除。
    • 不支持删除外部表的列。
    • 删除JSONB相关列后，相关JSONB索引会一并删除。
    • 删除proxima_vector列时，需要指定cascade参数。
    • 删除Serial列时，如果Sequence是基于这一列创建的，则Sequence会一并被删除。
    • 如果表创建了物化视图，不支持删除物化视图，也不支持删除源表中引用的物化视图列。
  • 使用语法
    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 * FROM tbl;

--返回结果
id    age    class    reg_timestamp
----+-----+---------+--------------

重命名列

Hologres从V1.1版本开始，支持重命名列，具体语法如下。
说明
  • 如果您的实例是V1.1以下版本，请您使用自助升级或加入实时数仓Hologres交流群（钉钉群号：32314975）申请升级实例。
  • 若您的表是分区表，由于存在分区子表和父表数据结构一致性的要求，仅支持重命名分区父表的列，不支持单独重命名某个分区子表的列。重命名分区父表的列，所有子表自动生效。
  • 不支持同时重命名多个表的列名称。
  • 仅只有表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]}[,...]]');
      重要 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，无需再重新建表即可修改表属性。

    • 使用语法
      --修改全量
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. 在表的详情页面，可视化修改表的字段和部分表属性。修改表
  5. 单击右上角的提交表，完成表修改。