ALTER TABLE

使用限制

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以下版本，請您使用常見升級準備失敗報錯或加入HologresDingTalk交流群反饋，詳情請參見如何擷取更多的線上支援？。
- 若您的表是分區表，僅能刪除分區父表的列，無法直接刪除分區子表的列。對於分區子表，在父表的列刪除後自動刪除。此操作開銷較高，建議在業務低峰期執行。
- 僅有表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以下版本，請您使用常見升級準備失敗報錯或加入HologresDingTalk交流群反饋，詳情請參見如何擷取更多的線上支援？。
若您的表是分區表，由於存在分區子表和父表資料結構一致性的要求，僅支援重新命名分區父表的列，不支援單獨重新命名某個分區子表的列。重新命名分區父表的列，所有子表自動生效。
不支援同時重新命名多個表的列名稱。
僅表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頁面頂部功能表列，單擊元數據管理。
在元數據管理頁面左側的已登入執行個體列表，雙擊要修改的目標表。
在表的詳情頁面，可視化修改表的欄位和部分表屬性。

在欄位標籤頁中，可查看錶的所有欄位資訊（欄位名、資料類型、主鍵、可空、預設值等）。單擊底部添加欄位按鈕，在新增行中輸入欄位名並選擇資料類型，完成後單擊右上方提交儲存修改。
單擊右上方的提交，完成表修改。