You can execute the ALTER TABLE statement to modify a table. Modifications made to a parent partitioned table are automatically applied to its child partitioned tables. This topic describes how to use the ALTER TABLE statement.

Limits

Take note of the following items:
  • For an existing table, you can rename the table, create fields in the table, and modify the time-to-live (TTL) period of the table.
  • You can configure the DEFAULT constraint for a field. You can also modify the dictionary_encoding_columns and bitmap_columns properties for a field.
  • You cannot change data types in tables.

Rename a 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
    -- Rename an internal table.
    ALTER TABLE [schema_name.]<table_name> RENAME TO <new_table_name>;
    
    -- Rename a foreign table.
    ALTER FOREIGN TABLE [schema_name.]<foreign_table_name> RENAME TO <new_foreign_table_name>;
  • Examples
    -- Rename the holo_test internal table in the public schema to holo_test_1.
    ALTER TABLE public.holo_test RENAME TO holo_test_1 ;
    
    -- Rename the foreign_holo_test foreign table in the public schema to foreign_holo_test_1.
    ALTER FOREIGN TABLE public.foreign_holo_test RENAME TO foreign_holo_test_1;

Create fields

You can execute the ALTER TABLE statement to create fields following the last field in a table.

  • Syntax
    -- Create a single field.
    ALTER TABLE IF EXISTS [schema_name.]<table_name> ADD COLUMN <new_column> <data_type>;
    
    -- Create multiple fields at a time.
    ALTER TABLE IF EXISTS [schema_name.]<table_name> ADD COLUMN <new_column_1> <data_type>, ADD COLUMN <new_column_2> <data_type>; 
  • Example
    -- Create a field named id in the holo_test table.
    ALTER TABLE IF EXISTS public.holo_test ADD COLUMN id int;

Rename fields

Hologres V1.1 and later allow you to rename the fields of a table.
Note
  • To upgrade a Hologres instance from a version earlier than V1.1 to V1.1 or later, submit a ticket or join the Hologres DingTalk group for technical support.
  • If your tables are partitioned tables, the structures of data in parent and child partitioned tables must be consistent. Only the fields of parent partitioned tables can be renamed. The fields of child partitioned tables cannot be renamed. If the fields of a child partitioned table are used in its parent partitioned table, the fields of the child partitioned table are automatically renamed to match those of the parent partitioned table.
  • You cannot rename the fields of different tables at a time.
  • The fields 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 fields of the table.
  • Syntax
    ALTER TABLE [schema_name.]<table_name> RENAME COLUMN <old_column_name> TO <new_column_name>;
  • Example
    -- Rename the id field to name in the holo_test table.
    ALTER TABLE public.holo_test RENAME COLUMN id TO name; 

Configure the DEFAULT constraint for a field

You can execute the ALTER TABLE statement to configure the DEFAULT constraint for a field 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 field after the configuration, but does not affect existing data of the field in the table. You can configure the DEFAULT constraint for a field 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
    -- Specify a DEFAULT constraint for a field in a table.
    ALTER TABLE [schema_name.]<table_name> ALTER COLUMN <column> SET DEFAULT <expression>;
    
    -- Remove the DEFAULT constraint for a field in a table.
    ALTER TABLE [schema_name.]<table_name> ALTER COLUMN <column> DROP DEFAULT;
  • Examples
    -- Specify a DEFAULT constraint for the id field in the holo_test table to set the default field value to 0.
    ALTER TABLE holo_test ALTER COLUMN id SET DEFAULT 0;
    
    -- Remove the DEFAULT constraint for the id field in the holo_test table.
    ALTER TABLE holo_test ALTER COLUMN id DROP DEFAULT;

Modify the properties of a table

Hologres allows you to modify table properties by modifying relevant parameters. You can use the following methods to modify table properties:

  • Change the setting of dictionary_encoding_columns. When you change the dictionary encoding settings, data files are re-encoded for storage, which consumes CPU and memory resources. We recommend that you modify the dictionary encoding settings during off-peak hours.
    • Syntax
      -- Set the dictionary encoding property for all fields.
      CALL SET_TABLE_PROPERTY('[schema_name.]<table_name>', 'dictionary_encoding_columns', '[columnName{:[onoffauto]}[,...]]');
      
      -- Change the dictionary encoding property only for the fields that you specify in the CALL statement. The dictionary encoding property remains unchanged for other fields.
      CALL UPDATE_TABLE_PROPERTY('[schema_name.]<table_name>', 'dictionary_encoding_columns', '[columnName{:[onoffauto]}[,...]]');
    • Parameters
      Parameter Description
      table_name The name of the table to be modified. Enter the table name as it is. The table name can contain the schema information about the table.
      on Enables dictionary encoding for the field.
      off Disables dictionary encoding for the field.
      auto Specifies that Hologres determines whether to enable dictionary encoding for the field. If you use this parameter for a field, Hologres determines whether to enable dictionary encoding for the field based on the recurrence of the values of this field. Dictionary encoding is more suitable for fields that have higher recurrence of field values. By default, Hologres V0.8 and earlier enable dictionary encoding for all fields of the TEXT data type. Hologres V0.9 and later determine whether to enable dictionary encoding for a field based on the characteristics of the values of the field.
    • Examples
      • In the following sample statements, dictionary encoding is enabled for the a field. Hologres determines whether to enable dictionary encoding for the b field. The dictionary encoding property remains unchanged for the 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','dictionary_encoding_columns','a:on,b:auto');
      • In the following sample statements, dictionary encoding is disabled for the a field. Hologres enables dictionary encoding 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 SET_TABLE_PROPERTY('dwd.holo_test','dictionary_encoding_columns','a:off');
  • Change the setting of bitmap_columns.

    Hologres V0.9 and later allow you to modify the bitmap index property of fields without the need to create tables.

    • Syntax
      -- Set the bitmap index property for all fields.
      CALL SET_TABLE_PROPERTY('[schema_name.]<table_name>', 'bitmap_columns', '[columnName{:[onoff]}[,...]]');
      
      -- Change the bitmap index property only for the fields that you specify in the CALL statement. The bitmap index property remains unchanged for other fields.
      CALL UPDATE_TABLE_PROPERTY('[schema_name.]<table_name>', 'bitmap_columns', '[columnName{:[onoff]}[,...]]');
    • Parameters
      Parameter Description
      table_name The name of the table to be modified. Enter the table name as it is. The table name can contain the schema information about the table.
      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 index 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');
  • Change the setting of time_to_live_in_seconds.
    • Syntax
      call set_table_property('[schema_name.]<table_name>', 'time_to_live_in_seconds', '<non_negative_literal>');
    • Parameters
      Parameter Description
      time_to_live_in_seconds The property to be modified. This property specifies the TTL period of the table, in seconds. The value must be a non-negative integer or floating-point number.
      Note The TTL period of a Hologres table starts from the time when data was first written to the table. If no operations are performed on the table within the specified TTL period, data in the table is deleted after the TTL period ends.
    • Example
      call set_table_property('dwd.holo_test', 'time_to_live_in_seconds', '600');

Modify a table in the HoloWeb console

HoloWeb allows you to modify tables in a visualized manner. You can modify fields and other properties of tables without the need to execute SQL statements. Perform the following steps:

  1. Log on to the HoloWeb console. For more information, see HoloWeb quick start.
  2. In the top navigation bar of the HoloWeb console, click Metadata Management.
  3. On the Metadata Management tab, click Instances Connected in the left-side navigation pane and double-click the table that you want to modify.
  4. On the details tab of the table, modify the fields and other properties of the table. Modify a table
  5. Click Submit in the upper-right corner.