ALTERTABLE的用法-实时数仓 Hologres(Hologres)-阿里云帮助中心

使用限制

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

Hologres单表最多支持6400列。通过ALTER TABLE ADD COLUMN增加列时，表的总列数不能超过此上限。
目前支持对表进行重命名、增加列和修改表数据生存时间的操作。
支持修改字段的默认值、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>;

使用示例

--建表（class列初始类型为varchar(10)）
BEGIN;
CREATE TABLE tbl (
  "id" bigint NOT NULL,
  "name" text NOT NULL,
  "age" bigint,
  "class" varchar(10) 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');
COMMIT;

--插入示例数据
INSERT INTO tbl VALUES (1, 'Alice', 18, 'class1', '2024-01-01 10:00:00');
INSERT INTO tbl VALUES (2, 'Bob', 20, 'class2', '2024-01-02 11:00:00');

--查看修改前class列的类型
SELECT column_name, data_type, character_maximum_length
FROM information_schema.columns
WHERE table_schema = 'public' AND table_name = 'tbl' AND column_name = 'class';
--返回：class | character varying | 10

--修改class列的数据类型为text
ALTER TABLE tbl ALTER COLUMN class TYPE text;

--查看修改后class列的类型
SELECT column_name, data_type, character_maximum_length
FROM information_schema.columns
WHERE table_schema = 'public' AND table_name = 'tbl' AND column_name = 'class';
--返回：class | text | NULL

重命名

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

说明

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

使用语法

--内部表重命名
ALTER TABLE [IF EXISTS] [<schema_name>.]<table_name> RENAME TO <new_table_name>;

--外部表重命名
ALTER FOREIGN TABLE [IF EXISTS] [<schema_name>.]<foreign_table_name> RENAME TO <new_foreign_table_name>;

使用示例

--建表并插入示例数据
CREATE TABLE public.holo_test (id bigint, name text);
INSERT INTO public.holo_test VALUES (1, 'Alice'), (2, 'Bob');

--查看重命名前的表名
SELECT table_name FROM information_schema.tables
WHERE table_schema='public' AND table_name IN ('holo_test', 'holo_test_1');
--返回：holo_test

--将表holo_test重命名为holo_test_1
ALTER TABLE IF EXISTS public.holo_test RENAME TO holo_test_1;

--查看重命名后的表名
SELECT table_name FROM information_schema.tables
WHERE table_schema='public' AND table_name IN ('holo_test', 'holo_test_1');
--返回：holo_test_1

--验证数据保留
SELECT * FROM public.holo_test_1 ORDER BY id;
--返回：1, Alice / 2, Bob

--将外部表foreign_holo_test重命名为foreign_holo_test_1
ALTER FOREIGN TABLE IF EXISTS 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>;

使用示例

--建表并插入示例数据
CREATE TABLE public.holo_test (name text);
INSERT INTO public.holo_test VALUES ('Alice'), ('Bob');

--查看新增列前的列结构
SELECT column_name, data_type FROM information_schema.columns
WHERE table_schema='public' AND table_name='holo_test' ORDER BY ordinal_position;
--返回：name | text

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

--查看新增id列后的列结构
SELECT column_name, data_type FROM information_schema.columns
WHERE table_schema='public' AND table_name='holo_test' ORDER BY ordinal_position;
--返回：name | text
--      id   | integer

--一次新增多列
ALTER TABLE IF EXISTS public.holo_test ADD COLUMN age int, ADD COLUMN city text;

--查看新增多列后的列结构
SELECT column_name, data_type FROM information_schema.columns
WHERE table_schema='public' AND table_name='holo_test' ORDER BY ordinal_position;
--返回：name | text
--      id   | integer
--      age  | integer
--      city | text

删除列（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;

--插入示例数据
INSERT INTO tbl VALUES (1, 'Alice', 18, 'class1', '2024-01-01 10:00:00');
INSERT INTO tbl VALUES (2, 'Bob', 20, 'class2', '2024-01-02 11:00:00');

--删除指定列
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
----+-----+--------+------------------------
  1 |  18 | class1 | 2024-01-01 10:00:00+08
  2 |  20 | class2 | 2024-01-02 11:00:00+08
(2 rows)

重命名列

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

说明

如果您的实例是V1.1以下版本，请您使用常见升级准备失败报错或加入Hologres钉钉交流群反馈，详情请参见如何获取更多的在线支持？。
若您的表是分区表，由于存在分区子表和父表数据结构一致性的要求，仅支持重命名分区父表的列，不支持单独重命名某个分区子表的列。重命名分区父表的列，所有子表自动生效。
不支持同时重命名多个表的列名称。
仅表Owner能重命名列，若您的数据库使用的是简单权限模型，需要设置为developer用户组权限。

使用语法

ALTER TABLE [IF EXISTS] [<schema_name>.]<table_name> RENAME COLUMN <old_column_name> TO <new_column_name>;

使用示例

--建表并插入示例数据
CREATE TABLE public.holo_rename_col (id bigint, age int);
INSERT INTO public.holo_rename_col VALUES (1, 18), (2, 20);

--查看重命名前的列名
SELECT column_name FROM information_schema.columns
WHERE table_schema='public' AND table_name='holo_rename_col' ORDER BY ordinal_position;
--返回：id
--      age

--将holo_rename_col表的age列重命名为user_age
ALTER TABLE IF EXISTS public.holo_rename_col RENAME COLUMN age TO user_age;

--查看重命名后的列名
SELECT column_name FROM information_schema.columns
WHERE table_schema='public' AND table_name='holo_rename_col' ORDER BY ordinal_position;
--返回：id
--      user_age

--验证数据保留
SELECT * FROM public.holo_rename_col ORDER BY id;
--返回：1, 18
--      2, 20

修改默认值

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

使用语法

--修改表字段的默认值
ALTER TABLE [IF EXISTS] [<schema_name>.]<table_name> ALTER COLUMN <column> SET DEFAULT <expression>;

--删除表字段的默认值
ALTER TABLE [IF EXISTS] [<schema_name>.]<table_name> ALTER COLUMN <column> DROP DEFAULT;

使用示例

--建表并插入一条不带默认值的初始数据
CREATE TABLE public.holo_default (id int, name text);
INSERT INTO public.holo_default (name) VALUES ('Alice');

--查看初始默认值
SELECT column_name, column_default FROM information_schema.columns
WHERE table_schema='public' AND table_name='holo_default' ORDER BY ordinal_position;
--返回：id,   NULL
--      name, NULL

--修改id列的默认值为0
ALTER TABLE IF EXISTS public.holo_default ALTER COLUMN id SET DEFAULT 0;

--查看修改后的默认值
SELECT column_name, column_default FROM information_schema.columns
WHERE table_schema='public' AND table_name='holo_default' ORDER BY ordinal_position;
--返回：id,   0
--      name, NULL

--插入新数据验证默认值仅对新数据生效
INSERT INTO public.holo_default (name) VALUES ('Bob');
SELECT * FROM public.holo_default ORDER BY name;
--返回：Alice, NULL   （SET DEFAULT 前插入，不补默认值）
--      Bob,   0      （SET DEFAULT 后插入，自动填充默认值0）

--删除id列的默认值
ALTER TABLE IF EXISTS public.holo_default ALTER COLUMN id DROP DEFAULT;

--查看删除后的默认值
SELECT column_name, column_default FROM information_schema.columns
WHERE table_schema='public' AND table_name='holo_default' ORDER BY ordinal_position;
--返回：id,   NULL
--      name, NULL

修改表属性

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

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

使用语法

-- 修改dictionary_encoding_columns（2.1版本起）
ALTER TABLE [IF EXISTS] [<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版本及之后版本，会根据数据特征自动选择是否创建字典编码。

使用示例

使用UPDATE_TABLE_PROPERTY增量修改：对a列显式创建dictionary，b列自动选择是否创建dictionary，其余字段保持不变。

--建表
CREATE TABLE public.holo_dict_test (
 a text NOT NULL,
 b text NOT NULL,
 c text NOT NULL,
 d text
);

--查看修改前的dictionary_encoding_columns属性
SELECT property_value FROM hologres.hg_table_properties
WHERE table_name='holo_dict_test' AND property_key='dictionary_encoding_columns';
--返回：a:auto,b:auto,c:auto,d:auto（新建表所有text列默认为auto）

--增量修改：对a列显式开启，b列自动选择
CALL UPDATE_TABLE_PROPERTY('public.holo_dict_test','dictionary_encoding_columns','a:on,b:auto');

--查看修改后的dictionary_encoding_columns属性
SELECT property_value FROM hologres.hg_table_properties
WHERE table_name='holo_dict_test' AND property_key='dictionary_encoding_columns';
--返回：b:auto,c:auto,d:auto,a（仅a、b按指定值修改，c、d保持不变；:on为默认值故存储时省略后缀，显式修改的列会移至列表末尾）

使用SET_TABLE_PROPERTY全量覆盖：对a列显式关闭dictionary，未列出的字段会被清空dictionary设置。

--建表
CREATE TABLE public.holo_dict_test_2 (
 a text NOT NULL,
 b text NOT NULL,
 c text NOT NULL,
 d text
);

--查看修改前的dictionary_encoding_columns属性
SELECT property_value FROM hologres.hg_table_properties
WHERE table_name='holo_dict_test_2' AND property_key='dictionary_encoding_columns';
--返回：a:auto,b:auto,c:auto,d:auto

--全量修改：仅指定a列关闭，其他列不在列表中
CALL SET_TABLE_PROPERTY('public.holo_dict_test_2','dictionary_encoding_columns','a:off');

--查看修改后的dictionary_encoding_columns属性
SELECT property_value FROM hologres.hg_table_properties
WHERE table_name='holo_dict_test_2' AND property_key='dictionary_encoding_columns';
--返回：b:auto,c:auto,d:auto（a:off会将a列从列表中移除，未列出的b、c、d字段被重置为auto）

修改bitmap_columns比特编码列

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

使用语法

-- 修改bitmap_columns（2.1版本起）
ALTER TABLE [IF EXISTS] [<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。

使用示例

使用UPDATE_TABLE_PROPERTY增量修改：仅对a列关闭bitmap索引，b、c、d列保持不变。新建表所有列默认自动开启bitmap索引。

--建表
CREATE TABLE public.holo_bitmap_test (
 a text NOT NULL,
 b text NOT NULL,
 c text NOT NULL,
 d text
);

--查看修改前的bitmap_columns属性
SELECT property_value FROM hologres.hg_table_properties
WHERE table_name='holo_bitmap_test' AND property_key='bitmap_columns';
--返回：a,b,c,d（新建表所有列默认开启，:on为默认值故存储时省略后缀）

--增量修改：仅对a列关闭bitmap索引
CALL UPDATE_TABLE_PROPERTY('public.holo_bitmap_test','bitmap_columns','a:off');

--查看修改后的bitmap_columns属性
SELECT property_value FROM hologres.hg_table_properties
WHERE table_name='holo_bitmap_test' AND property_key='bitmap_columns';
--返回：b,c,d（a列已关闭并从列表移除，b、c、d保持默认开启，:on为默认值故省略后缀）

使用SET_TABLE_PROPERTY全量覆盖：仅对b列开启bitmap索引，未列出的a、c、d列不再有bitmap索引。

--建表
CREATE TABLE public.holo_bitmap_test_2 (
 a text NOT NULL,
 b text NOT NULL,
 c text NOT NULL,
 d text
);

--查看修改前的bitmap_columns属性
SELECT property_value FROM hologres.hg_table_properties
WHERE table_name='holo_bitmap_test_2' AND property_key='bitmap_columns';
--返回：a,b,c,d（新建表所有列默认开启，:on为默认值故存储时省略后缀）

--全量修改：仅指定b列开启，其他列不在列表中
CALL SET_TABLE_PROPERTY('public.holo_bitmap_test_2','bitmap_columns','b:on');

--查看修改后的bitmap_columns属性
SELECT property_value FROM hologres.hg_table_properties
WHERE table_name='holo_bitmap_test_2' AND property_key='bitmap_columns';
--返回：b（未列出的a、c、d列被清空bitmap索引设置，:on为默认值故省略后缀）

修改表数据的生存时间

使用语法

call set_table_property('[<schema_name>.]<table_name>', 'time_to_live_in_seconds', '<non_negative_literal>');

参数说明

参数	说明
time_to_live_in_seconds	简称TTL，表数据的生存时间，单位为秒，必须是正整数，且不能小于86400（1天）。

说明

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

使用示例

--建表
CREATE TABLE public.holo_ttl_test (
 id bigint,
 name text
);

--查看修改前的TTL属性
SELECT property_value FROM hologres.hg_table_properties
WHERE table_name='holo_ttl_test' AND property_key='time_to_live_in_seconds';
--返回：3153600000（默认值，约100年）

--修改TTL为1天（86400秒）
CALL SET_TABLE_PROPERTY('public.holo_ttl_test', 'time_to_live_in_seconds', '86400');

--查看修改后的TTL属性
SELECT property_value FROM hologres.hg_table_properties
WHERE table_name='holo_ttl_test' AND property_key='time_to_live_in_seconds';
--返回：86400（TTL已生效，即1天）

修改表所在Schema

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

使用语法
```
ALTER TABLE [IF EXISTS] [<schema_name>.]<table_name>
    SET SCHEMA <new_schema>;
```
schema_name为表所在的Schema名称；table_name为修改表的名称；new_schema为移动至新Schema的名称。

使用示例

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

--创建目标Schema
CREATE SCHEMA IF NOT EXISTS testschema;

--建表并插入示例数据
CREATE TABLE public.schema_demo (
 id bigint PRIMARY KEY,
 name text
);
INSERT INTO public.schema_demo VALUES (1, 'alice'), (2, 'bob');

--查看移动前表所在的Schema
SELECT table_schema, table_name FROM information_schema.tables
WHERE table_name='schema_demo';
--返回：public | schema_demo

--将表从public移动至testschema
ALTER TABLE IF EXISTS public.schema_demo SET SCHEMA testschema;

--查看移动后表所在的Schema
SELECT table_schema, table_name FROM information_schema.tables
WHERE table_name='schema_demo';
--返回：testschema | schema_demo

--验证数据完整保留
SELECT * FROM testschema.schema_demo ORDER BY id;
--返回：1, alice / 2, bob

HoloWeb可视化修改表

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

进入HoloWeb页面，详情请参见连接HoloWeb并执行查询。
在HoloWeb页面顶部菜单栏，单击元数据管理。
在元数据管理页面左侧的已登录实例列表，双击要修改的目标表。
在表的详情页面，可视化修改表的字段和部分表属性。
单击右上角的提交，完成表修改。