Hologres:ALTER TABLE

Limits

Usage notes

Modifying table properties such as dictionary_encoding_columns, bitmap_columns, and time_to_live_in_seconds may trigger an asynchronous compaction process in the background. This process consumes CPU resources, and the storage usage of the instance may temporarily increase before it decreases.

Change data types

Hologres V3.0 and later supports changing the data types of columns in internal tables.

• Limits

  • You can change the data types of columns in non-partitioned tables and parent partitioned tables, but not in child partitioned tables.

  • You cannot change the data types of partition key columns in a parent partitioned table.

  • The COLLATE and USING clauses are not supported.

  • Only the following data type conversions are supported:

    Source data type	Destination type	Notes
    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>;

• Example

  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

Add columns

You can use the ALTER TABLE statement to add columns. New columns can only be added after the last existing column.

• Syntax

  -- Add a 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>; 

• Example

  -- Add the id column to the holo_test table.
  ALTER TABLE IF EXISTS public.holo_test ADD COLUMN id int;

Drop columns (Beta)

Hologres V2.0 and later supports dropping columns. The syntax is as follows.

• Limits

  • This feature is available only for Hologres V2.0 and later. If your instance is an earlier version, see Common upgrade preparation failures or join the Hologres DingTalk group for feedback. For more information, see How can I get more online support?.

  • If your table is a partitioned table, you can only drop columns from the parent table, not directly from a child table. Columns are automatically dropped from child tables after they are dropped from the parent table. This is a high-overhead operation. We recommend that you perform this operation during off-peak hours.

  • Only the table owner can drop columns. If your database uses the simple permission model, the owner must be granted permissions for the developer user group.

  • You cannot drop columns that are set as a Primary Key, Distribution Key, Clustering Key, or Event_time_column.

  • You cannot drop columns from foreign tables.

  • When you drop columns that have a JSONB data type, the associated JSONB indexes are also dropped.

  • To drop a proxima_vector column, you must specify the cascade parameter.

  • When you drop a Serial column, any Sequence created based on that column is also dropped.

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

• Syntax

  Important

  Dropping columns is not supported in Hologres versions earlier than V2.0.

  set hg_experimental_enable_drop_column = on; -- Enable this feature using this GUC parameter.
  ALTER TABLE IF EXISTS <table_name> DROP COLUMN  [ IF EXISTS ] <column> [ RESTRICT | CASCADE ]

• Example

  -- 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 beta. You must enable it using a GUC parameter.
  ALTER TABLE IF EXISTS tbl DROP COLUMN name;

  Query the table:

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

Rename columns

• Syntax

  ALTER TABLE [IF EXISTS] [<schema_name>.]<table_name> RENAME COLUMN <old_column_name> TO <new_column_name>;

• Example

  -- Rename the id column of the holo_test table to name.
  ALTER TABLE IF EXISTS public.holo_test RENAME COLUMN id TO name; 

Modify a default value

You can use the ALTER TABLE statement to modify the default value of a column. The default value can be a constant or a constant expression. The new default value applies only to data that is written or updated after the modification and does not affect existing data in the table. This feature is supported only in Hologres V0.9.23 and later. The syntax is as follows:

• Syntax

  -- Modify the default value of a table column.
  ALTER TABLE [IF EXISTS] [<schema_name>.]<table_name> ALTER COLUMN <column> SET DEFAULT <expression>;
  
  -- Drop the default value of a table column.
  ALTER TABLE [IF EXISTS] [<schema_name>.]<table_name> ALTER COLUMN <column> DROP DEFAULT;

• Example

  -- Set the default value of the id column in the holo_test table to 0.
  ALTER TABLE IF EXISTS holo_test ALTER COLUMN id SET DEFAULT 0;
  
  -- Drop the default value of the id column in the holo_test table.
  ALTER TABLE IF EXISTS holo_test ALTER COLUMN id DROP DEFAULT;

Modify table properties

Hologres lets you modify table properties by running statements that modify parameters. The syntax is as follows:

• Modify the dictionary_encoding_columns property. This action re-encodes the data files, which consumes CPU and memory resources. We recommend that you perform this change during off-peak hours.

  • Syntax

    -- Modify dictionary_encoding_columns (for V2.1 and later)
    ALTER TABLE [IF EXISTS] [<schema_name>.]<table_name> SET (dictionary_encoding_columns = '[columnName{:[on|off|auto]}[,...]]'); -- Only full modification is supported.
    
    -- Modify dictionary_encoding_columns (for all versions)
    -- Full modification
    CALL SET_TABLE_PROPERTY('[<schema_name>.]<table_name>', 'dictionary_encoding_columns', '[columnName{:[on|off|auto]}[,...]]');
    
    -- Incremental modification. Modifies only the specified columns in the call. Other columns remain unchanged.
    CALL UPDATE_TABLE_PROPERTY('[<schema_name>.]<table_name>', 'dictionary_encoding_columns', '[columnName{:[on|off|auto]}[,...]]');

    Important

    Hologres V2.0 and later optimizes the UPDATE_TABLE_PROPERTY syntax. If you run the following statement, the table's dictionary_encoding_columns property remains unchanged. In versions earlier than Hologres V2.0, this statement clears the table's dictionary_encoding_columns property.

    CALL UPDATE_TABLE_PROPERTY('<table_name>','dictionary_encoding_columns','');

  • Parameter description

    Parameter	Description
    table_name	The name of the table to modify. The table name is case-sensitive and can include the schema name.
    on	Enables dictionary_encoding_columns for the current column.
    off	Disables dictionary_encoding_columns for the current column.
    auto	Automatic. If you set this parameter to auto, Hologres automatically decides whether to apply dictionary_encoding_columns based on the repetition of values in the column. The higher the value repetition, the greater the benefit of dictionary encoding. In Hologres V0.8 and earlier, all text columns have dictionary_encoding_columns enabled by default. In Hologres V0.9 and later, Hologres automatically determines whether to create a dictionary encoding based on data characteristics.

  • Examples

    • Explicitly create a dictionary for column a, allow the system to automatically decide for column b, and do not create dictionaries 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');

    • Explicitly disable the dictionary for column a. The system automatically creates dictionary indexes 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');

• Modify the bitmap_columns parameter for bit-encoded columns

  In Hologres V0.9 and later, you can run the following statements to modify the bitmap_columns property without recreating the table.

  • Syntax

    -- Modify bitmap_columns (for V2.1 and later)
    ALTER TABLE [IF EXISTS] [<schema_name>.]<table_name> SET (bitmap_columns = '[columnName{:[on|off]}[,...]]');
    
    -- Modify bitmap_columns (for all versions)
    -- Full modification
    CALL SET_TABLE_PROPERTY('[<schema_name>.]<table_name>', 'bitmap_columns', '[columnName{:[on|off]}[,...]]');
    
    -- Incremental modification. Modifies only the specified columns in the call. Other columns remain unchanged.
    CALL UPDATE_TABLE_PROPERTY('[<schema_name>.]<table_name>', 'bitmap_columns', '[columnName{:[on|off]}[,...]]');

    Important

    Hologres V2.0 and later optimizes the UPDATE_TABLE_PROPERTY syntax. If you run the following statement, the table's bitmap_columns property remains unchanged. In versions earlier than Hologres V2.0, this statement clears the table's bitmap_columns property.

    CALL UPDATE_TABLE_PROPERTY('<table_name>','bitmap_columns','');

  • Parameter description

    Parameter	Description
    table_name	The name of the table to modify. The table name is case-sensitive and can include the schema name.
    on	Enables bitmap_columns for the current column.
    off	Disables bitmap_columns for the current column.

  • Examples

    • Enable the bitmap index for column a. Do not enable bitmap indexes 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 UPDATE_TABLE_PROPERTY('dwd.holo_test','bitmap_columns','a:on');

    • Disable the bitmap index for column b. The system automatically creates bitmap indexes for columns a, c, and d.

      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');

• Modify the TTL of table data

  • 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	Abbreviated as TTL. The survival time of table data, in seconds. The value must be a positive integer.

    Note

    The TTL of table data is calculated from the time the data is written to Hologres. After the specified TTL period expires, the data is deleted within a certain time frame, not at a precise moment.

  • Example

    call set_table_property('dwd.holo_test', 'time_to_live_in_seconds', '600');

Change the schema of a table

Hologres V1.3 and later supports changing the schema of a table. For example, you can move a table from schema1 to schema2 without recreating the table or importing data. This lets you quickly change the table path.

• Syntax

  ALTER TABLE [IF EXISTS] [<schema_name>.]<table_name>
      SET SCHEMA <new_schema>;

  schema_name is the name of the schema where the table resides. table_name is the name of the table to modify. new_schema is the name of the new schema.

• Example

  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 using the HoloWeb visual editor

HoloWeb provides a visual editor that you can use to modify table fields and some table properties without writing SQL commands. The steps are as follows:

1. Go to the HoloWeb page. For more information, see Connect to HoloWeb and run queries.

2. On the HoloWeb page, click Metadata Management in the top menu bar.

3. On the Metadata Management page, in the Instances Connected list on the left, double-click the table that you want to modify.

4. On the table details page, you can visually modify the table fields and some table properties.

   

5. In the upper-right corner, click Submit to save your changes.