All Products
Search
Document Center

Hologres:ALTER TABLE

Last Updated:Dec 20, 2023

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.

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

    -- Change the name of the holo_test internal table in the public schema to holo_test_1.
    ALTER TABLE public.holo_test RENAME TO holo_test_1 ;
    
    -- Change the name of 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;

Add columns

You can execute the ALTER TABLE statement to add columns that follow the last column in a table.

  • Syntax

    -- Add a single column.
    ALTER TABLE IF EXISTS [schema_name.]<table_name> ADD COLUMN <new_column> <data_type>;
    
    -- Add multiple columns.
    ALTER TABLE IF EXISTS [schema_name.]<table_name> ADD COLUMN <new_column_1> <data_type>, ADD COLUMN <new_column_2> <data_type>; 
  • Examples

    -- Add a column named id in the holo_test table.
    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.

  • Limits

    • You can drop columns from tables in Hologres instances of V2.0 or later. If the version of your Hologres instance is earlier than V2.0, 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, you can use the ALTER TABLE statement to drop the columns of the parent table rather than the columns of a specific child table. After you execute the statement to drop specific columns from the parent table, the columns are automatically dropped from the child tables. Dropping columns is a high-overhead operation. We recommend that you perform this operation during off-peak hours.

    • The columns of a table can be dropped only by the owner of the table. If the database in which your table resides uses the simple permission model (SPM), your account must be assigned the {db}_developer role before you can use the account to drop columns from the table.

    • Columns that are configured with the Primary Key, Distribution Key, Clustering Key, or Event_time_column property cannot be dropped.

    • You cannot drop columns from foreign tables.

    • If you drop columns of the JSONB data type, the indexes that are created for the columns are also dropped.

    • If you want to drop columns of the proxima_vector type, you need to specify the cascade parameter.

    • If you drop a column of the SERIAL data type, the sequence that is created based on the column is also dropped.

    • If a materialized view is created for a table, you cannot drop the materialized view or the columns of the materialized view that are referenced in the source table.

  • Syntax

    Important

    You cannot drop columns for instances earlier than Hologres V2.0.

    set hg_experimental_enable_drop_column = on; -- Configure the Global User Configuration (GUC) parameter to enable this feature.
    ALTER TABLE IF EXISTS <table_name> DROP COLUMN  [ IF EXISTS ] <column> [ RESTRICT | CASCADE ]
  • Examples

    -- Create a table.
    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;
    
    -- Drop a specified column.
    set hg_experimental_enable_drop_column =on; -- This feature is in public preview. To enable the feature, set the GUC parameter.
    ALTER TABLE IF EXISTS tbl DROP COLUMN name;

    Query data from the table.

    SELECT*FROMtbl;
    
    -- The following result is returned:
    id    age    class    reg_timestamp
    ----+-----+---------+--------------
                        

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 [schema_name.]<table_name> RENAME COLUMN <old_column_name> TO <new_column_name>;
  • Examples

    -- Change the name of the id column in the holo_test table to name.
    ALTER TABLE 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

    -- Specify a value of the DEFAULT constraint for a column in a table.
    ALTER TABLE [schema_name.]<table_name> ALTER COLUMN <column> SET DEFAULT <expression>;
    
    -- Remove the value of the DEFAULT constraint for the column in the table.
    ALTER TABLE [schema_name.]<table_name> ALTER COLUMN <column> DROP DEFAULT;
  • Examples

    -- Specify the value of the DEFAULT constraint for the id column in the holo_test table as 0.
    ALTER TABLE holo_test ALTER COLUMN id SET DEFAULT 0;
    
    -- Remove the value of the DEFAULT constraint for the id column in the holo_test table.
    ALTER TABLE 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

    -- Modify the dictionary_encoding_columns property (applicable to Hologres V2.1 or later).
    ALTER TABLE <schema_name>.<table_name> SET (dictionary_encoding_columns = '[columnName{:[on|off|auto]}[,...]]'); -- The dictionary encoding property can be modified only for all columns.
    
    -- Modify the dictionary_encoding_columns property (applicable to all Hologres versions)
    -- Modify the dictionary encoding property for all columns.
    CALL SET_TABLE_PROPERTY('[schema_name.]<table_name>', 'dictionary_encoding_columns', '[columnName{:[on|off|auto]}[,...]]');
    
    -- Modify the dictionary encoding property only for the columns that you specify in the CALL statement. The dictionary encoding property remains unchanged for other columns.
    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

      -- Modify the bitmap_columns property (applicable to Hologres V2.1 or later).
      ALTER TABLE <schema_name>.<table_name> SET (bitmap_columns = '[columnName{:[on|off]}[,...]]');
      
      -- Modify the bitmap_columns property (applicable to all Hologres versions).
      -- Modify the bitmap_columns property for all columns.
      CALL SET_TABLE_PROPERTY('[schema_name.]<table_name>', 'bitmap_columns', '[columnName{:[on|off]}[,...]]');
      
      -- Modify the bitmap_columns property only for the columns that you specify in the CALL statement. The property remains unchanged for other columns.
      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>.]<table_name>
        SET SCHEMA <new_schema>;

    schema: 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:

  1. Log on to the HoloWeb console. For more information, see Connect to HoloWeb.

  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 properties of the table.修改表

  5. Click Submit in the upper-right corner.