You can execute the ALTER TABLE statement to modify a table. Modifications made to a parent table are automatically applied to its child tables. This topic describes how to use the ALTER TABLE statement.
Limits
When you modify a table in Hologres, take note of the following items:
You can rename a table, create columns in the table, and modify the time-to-live (TTL) period of the table.
You can configure the DEFAULT constraint for a column. You can also modify the dictionary_encoding_columns and bitmap_columns properties for the column.
You cannot modify the data types of columns in the table.
Usage notes
When you modify table properties such as dictionary_encoding_columns, bitmap_columns, and time_to_live_in_seconds, the operations may cause a compaction operation to be performed asynchronously at the backend. This consumes CPU resources. The operations may also cause the storage capacity of an instance to increase and then decrease.
Change data types
Hologres V3.0 and later allow you to change data types of columns in internal tables.
Limits
You can change data types of columns in non-partitioned tables and partitioned parent tables but not partitioned child tables.
You cannot change data types of partition key columns in partitioned parent tables.
The COLLATE and USING syntaxes are not supported.
The following table describes data type conversions that are supported.
Source data type | Destination data type | Description |
Source data type | Destination data type | Description |
VARCHAR(N) | VARCHAR(M) | M must be greater than N. |
VARCHAR(N) | TEXT | None. |
CHAR(N) | CHAR(M) | M must be greater than N. |
CHAR(N) | VARCHAR(M) | M must be greater than or equal to N. |
CHAR(N) | TEXT | None. |
JSON | TEXT | None. |
VARCHAR(N)[] | VARCHAR(M)[] | M must be greater than N. |
VARCHAR(N)[] | TEXT[] | None. |
Syntax
ALTER TABLE [IF EXISTS] [<schema_name>.]<table_name> ALTER [ COLUMN ] <column_name> TYPE <data_type>;
Examples
DROP TABLE IF EXISTS t;
CREATE TABLE IF NOT EXISTS t (
a varchar(5)
);
INSERT INTO t VALUES ('holo'), ('gres');
ALTER TABLE IF EXISTS t ALTER COLUMN a TYPE text;
Rename a foreign table
You can execute the ALTER TABLE statement to rename a table. If the table does not exist or the new table name is the same as the name of an existing table, an error is returned.
Note
You cannot rename a table across schemas.
Syntax
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>;
Examples
ALTER TABLE IF EXISTS public.holo_test RENAME TO holo_test_1 ;
ALTER FOREIGN TABLE IF EXISTS public.foreign_holo_test RENAME TO foreign_holo_test_1;
Add columns
You can execute the ALTER TABLE statement to add columns that follow the last column in a table.
Syntax
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>;
Examples
ALTER TABLE IF EXISTS public.holo_test ADD COLUMN id int;
Drop columns (in public preview)
In Hologres V2.0 and later, you can drop columns from a table.
Rename columns
Hologres V1.1 and later allow you to rename the columns of a table.
Note
If the version of your Hologres instance is earlier than V1.1, you can manually upgrade your Hologres instance in the Hologres console or join the Hologres DingTalk group to contact Hologres technical support. For more information about how to manually upgrade your Hologres instance in the Hologres console, see Manual upgrade (beta). For more information about how to obtain technical support, see Obtain online support for Hologres.
If your table is a partitioned table, the structures of data in the parent table and its child tables must be consistent. In this case, only the columns of the parent table can be renamed. The columns of the child tables cannot be renamed. If the columns of a child table are used in the parent table, the columns of the child table are automatically renamed to match the columns of the parent table.
You cannot rename the columns of different tables at a time.
The columns of a table can be renamed only by the owner of the table. If the database where your table resides uses the SPM, your account must be assigned the {db}_developer role before you can use the account to rename the columns of the table.
Syntax
ALTER TABLE [IF EXISTS] [<schema_name>.]<table_name> RENAME COLUMN <old_column_name> TO <new_column_name>;
Examples
ALTER TABLE IF EXISTS public.holo_test RENAME COLUMN id TO name;
Configure the DEFAULT constraint for a column
You can execute the ALTER TABLE statement to configure the DEFAULT constraint for a column in a table. The DEFAULT constraint can be a constant or a constant expression. The configuration takes effect only for data that is written to or updated in the column after the configuration but does not affect the existing data of the column in the table. You can configure the DEFAULT constraint for a column in a table that is created in only Hologres V0.9.23 and later. In this scenario, the ALTER TABLE statement uses the following syntax:
Syntax
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;
Examples
ALTER TABLE IF EXISTS holo_test ALTER COLUMN id SET DEFAULT 0;
ALTER TABLE IF EXISTS holo_test ALTER COLUMN id DROP DEFAULT;
Modify table properties
Hologres allows you to modify table properties by modifying relevant parameters in the ALTER TABLE statement. You can modify the following table properties:
dictionary_encoding_columns When you modify the dictionary encoding property, data files are re-encoded for storage, which consumes CPU and memory resources. We recommend that you modify the dictionary encoding property during off-peak hours.
Syntax
ALTER TABLE [IF EXISTS] [<schema_name>.]<table_name> SET (dictionary_encoding_columns = '[columnName{:[on|off|auto]}[,...]]');
CALL SET_TABLE_PROPERTY('[<schema_name>.]<table_name>', 'dictionary_encoding_columns', '[columnName{:[on|off|auto]}[,...]]');
CALL UPDATE_TABLE_PROPERTY('[<schema_name>.]<table_name>', 'dictionary_encoding_columns', '[columnName{:[on|off|auto]}[,...]]');
Important
Hologres V2.0 and later optimize the syntax of UPDATE_TABLE_PROPERTY. After the optimization, the dictionary_encoding_columns
property of a table remains unchanged during the execution of the following statement. In versions earlier than Hologres V2.0, the dictionary_encoding_columns
property of a table is cleared during the execution of the following statement.
CALL UPDATE_TABLE_PROPERTY('<table_name>','dictionary_encoding_columns','');
Parameter description
Parameter | Description |
table_name | The name of the table whose properties you want to modify. The table name is case-sensitive and can be preceded by a schema name. |
on | Enables dictionary encoding for a column. |
off | Disables dictionary encoding for a column. |
auto | Specifies whether to enable dictionary encoding for a column. If you set this parameter for a column, Hologres determines whether to enable dictionary encoding for a column based on the recurrence of the values of this column. Dictionary encoding is more suitable for the columns that have higher recurrence of values. In Hologres V0.8 and earlier, dictionary encoding is enabled for all columns of the TEXT data type by default. In Hologres V0.9 and later, Hologres determines whether to enable dictionary encoding for a column based on the characteristics of the values of the column. |
Examples
Enable dictionary encoding for column a, specify Hologres to determine whether to enable dictionary encoding for column b, and keep dictionary encoding unchanged for columns c and d.
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');
Disable dictionary encoding for column a. Hologres automatically enables dictionary encoding for columns b, c, and d.
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
In Hologres V0.9 and later, you can modify the bitmap_columns property of a table without the need to create another table.
Syntax
ALTER TABLE [IF EXISTS] [<schema_name>.]<table_name> SET (bitmap_columns = '[columnName{:[on|off]}[,...]]');
CALL SET_TABLE_PROPERTY('[<schema_name>.]<table_name>', 'bitmap_columns', '[columnName{:[on|off]}[,...]]');
CALL UPDATE_TABLE_PROPERTY('[<schema_name>.]<table_name>', 'bitmap_columns', '[columnName{:[on|off]}[,...]]');
Important
In Hologres V2.0 and later, the syntax of UPDATE_TABLE_PROPERTY is optimized. After the optimization, the bitmap_columns
property remains unchanged for columns in a table during the execution of the following statement. In versions earlier than Hologres V2.0, the bitmap_columns
property is cleared for columns in a table.
CALL UPDATE_TABLE_PROPERTY('<table_name>','bitmap_columns','');
Parameter description
Parameter | Description |
table_name | The name of the table whose properties you want to modify. The table name is case-sensitive and can be preceded by a schema name. |
on | Creates a bitmap index for the field. |
off | Does not create a bitmap index for the field. |
Examples
In the following sample statements, a bitmap index is created for the a field. The bitmap_columns property remains unchanged for the b, c, and d fields.
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');
In the following sample statements, no bitmap index is created for the b field. Hologres creates bitmap indexes for the a, c, and d fields.
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');
time_to_live_in_seconds property
Syntax
call set_table_property('[<schema_name>.]<table_name>', 'time_to_live_in_seconds', '<non_negative_literal>');
Parameter description
Parameter | Description |
time_to_live_in_seconds | The TTL period of the data in the table, in units of seconds. The value must be a positive integer. |
Note
The TTL period of a Hologres table starts from the time when data was first written to the table. After the TTL period expires, the data in the table is deleted within a period of time instead of at a specific point in time.
Examples
call set_table_property('dwd.holo_test', 'time_to_live_in_seconds', '600');
Change the schema where a table resides
Hologres V1.3 and later allow you to change the schema where a table resides. For example, you can move the table from schema1 to schema2 without the need to create another table. This way, you can change the path of a table in a quick manner.
Syntax
ALTER TABLE [IF EXISTS] [<schema_name>.]<table_name>
SET SCHEMA <new_schema>;
schema_name: the name of the schema where the table resides. table_name: the name of the table that you want to move to another schema. new_schema: the name of the schema to which the table is moved.
Examples
Move the table named tb1 from the public schema to the testschema schema.
ALTER TABLE IF EXISTS public.tbl
SET SCHEMA testschema;
Modify a table in the HoloWeb console
HoloWeb allows you to modify a table in a visualized manner. You can modify the fields and properties of the table without the need to execute SQL statements. To modify a table in the HoloWeb console, perform the following steps:
Log on to the HoloWeb console. For more information, see Connect to HoloWeb and perform queries.
In the top navigation bar of the HoloWeb console, click Metadata Management.
On the Metadata Management tab, click Instances Connected in the left-side navigation pane and double-click the table that you want to modify.
On the details tab of the table, modify fields and properties of the table.
Click Submit in the upper-right corner.