Hologres:ALTER TABLE

Limits

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
    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 t ALTER COLUMN a TYPE text;

Rename a foreign table

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 table or the columns that are referenced by the materialized view in the 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

• 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 and perform queries.

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 fields and properties of the table.

5. Click Submit in the upper-right corner.