Hologres:ALTER TABLE

Limitations

Usage notes

Modifying dictionary_encoding_columns, bitmap_columns, or time_to_live_in_seconds can trigger background compaction, which consumes CPU and may temporarily increase storage usage.

Modify data type

Starting from Hologres V3.0, you can modify column data types in internal tables.

• Limitations

  • Supported on non-partitioned tables and parent tables only. Child tables are not supported.

  • You cannot modify the data type of a partition column in a parent table.

  • The COLLATE and USING clauses are not supported.

  • Only the following data type conversions are supported:

    Source type	Target 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

  -- Create a table. The initial data type of the class column is varchar(10).
  BEGIN;
  CREATE TABLE tbl (
    "id" bigint NOT NULL,
    "name" text NOT NULL,
    "age" bigint,
    "class" varchar(10) 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');
  COMMIT;
  
  -- Insert sample data.
  INSERT INTO tbl VALUES (1, 'Alice', 18, 'class1', '2024-01-01 10:00:00');
  INSERT INTO tbl VALUES (2, 'Bob', 20, 'class2', '2024-01-02 11:00:00');
  
  -- Check the data type of the class column before modification.
  SELECT column_name, data_type, character_maximum_length
  FROM information_schema.columns
  WHERE table_schema = 'public' AND table_name = 'tbl' AND column_name = 'class';
  -- The following result is returned: class | character varying | 10
  
  -- Modify the data type of the class column to text.
  ALTER TABLE tbl ALTER COLUMN class TYPE text;
  
  -- Check the data type of the class column after modification.
  SELECT column_name, data_type, character_maximum_length
  FROM information_schema.columns
  WHERE table_schema = 'public' AND table_name = 'tbl' AND column_name = 'class';
  -- The following result is returned: class | text | NULL

Rename table

Add column

Adds columns to a table. New columns are appended after the last existing column.

• 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>; 

• Example

  -- Create a table and insert sample data.
  CREATE TABLE public.holo_test (name text);
  INSERT INTO public.holo_test VALUES ('Alice'), ('Bob');
  
  -- Check the schema before adding a column.
  SELECT column_name, data_type FROM information_schema.columns
  WHERE table_schema='public' AND table_name='holo_test' ORDER BY ordinal_position;
  -- The following result is returned: name | text
  
  -- Add the id column to the holo_test table.
  ALTER TABLE IF EXISTS public.holo_test ADD COLUMN id int;
  
  -- Check the schema after adding the id column.
  SELECT column_name, data_type FROM information_schema.columns
  WHERE table_schema='public' AND table_name='holo_test' ORDER BY ordinal_position;
  -- The following results are returned:
  -- name | text
  -- id   | integer
  
  -- Add multiple columns at once.
  ALTER TABLE IF EXISTS public.holo_test ADD COLUMN age int, ADD COLUMN city text;
  
  -- Check the schema after adding multiple columns.
  SELECT column_name, data_type FROM information_schema.columns
  WHERE table_schema='public' AND table_name='holo_test' ORDER BY ordinal_position;
  -- The following results are returned:
  -- name | text
  -- id   | integer
  -- age  | integer
  -- city | text

Drop column (Beta)

Starting from Hologres V2.0, you can drop a column from a table.

• Limitations

  • This feature is available only in Hologres V2.0 and later. If your instance runs a version earlier than V2.0, refer to troubleshoot common upgrade preparation errors or contact us for assistance. For details, see How do I get more online support?.

  • For partitioned tables, drop columns from the parent table only. The change propagates to all child tables automatically. This operation is resource-intensive. Perform it during off-peak hours.

  • Only the table owner can drop a column. If your database uses the Simple Permission Model (SPM), you must be a member of the developer user group.

  • You cannot drop a column that is set as a primary key, distribution key, clustering key, or event_time_column.

  • You cannot drop a column from a foreign table.

  • When you drop a JSONB column, its associated JSONB index is also dropped.

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

  • When you drop a Serial column, the sequence created based on this column is also dropped.

  • If a materialized view depends on a table, you cannot drop that table or any of its columns referenced by the view.

• Syntax

  Important

  Not supported in Hologres versions earlier than V2.0.

  set hg_experimental_enable_drop_column = on; -- Enable the feature by 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;
  
  -- Insert sample data.
  INSERT INTO tbl VALUES (1, 'Alice', 18, 'class1', '2024-01-01 10:00:00');
  INSERT INTO tbl VALUES (2, 'Bob', 20, 'class2', '2024-01-02 11:00:00');
  
  -- Drop the specified column.
  set hg_experimental_enable_drop_column = on;-- This feature is in beta. You must enable it by using a GUC parameter.
  ALTER TABLE IF EXISTS tbl DROP COLUMN name;

  Query the table:

  SELECT * FROM tbl;
  
  -- The following result is returned:
   id | age | class  |      reg_timestamp
  ----+-----+--------+------------------------
    1 |  18 | class1 | 2024-01-01 10:00:00+08
    2 |  20 | class2 | 2024-01-02 11:00:00+08
  (2 rows)

Rename column

• Syntax

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

• Example

  -- Create a table and insert sample data.
  CREATE TABLE public.holo_rename_col (id bigint, age int);
  INSERT INTO public.holo_rename_col VALUES (1, 18), (2, 20);
  
  -- Check the column names before renaming.
  SELECT column_name FROM information_schema.columns
  WHERE table_schema='public' AND table_name='holo_rename_col' ORDER BY ordinal_position;
  -- The following results are returned:
  -- id
  -- age
  
  -- Rename the age column in the holo_rename_col table to user_age.
  ALTER TABLE IF EXISTS public.holo_rename_col RENAME COLUMN age TO user_age;
  
  -- Check the column names after renaming.
  SELECT column_name FROM information_schema.columns
  WHERE table_schema='public' AND table_name='holo_rename_col' ORDER BY ordinal_position;
  -- The following results are returned:
  -- id
  -- user_age
  
  -- Verify that the data is retained.
  SELECT * FROM public.holo_rename_col ORDER BY id;
  -- The following results are returned:
  -- 1, 18
  -- 2, 20

Modify default value

Modifies the default value of a column to a constant or constant expression. This affects only subsequent writes and does not change existing data. Available in Hologres V0.9.23 and later.

• 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

  -- Create a table and insert an initial row without a default value for the id column.
  CREATE TABLE public.holo_default (id int, name text);
  INSERT INTO public.holo_default (name) VALUES ('Alice');
  
  -- Check the initial default values.
  SELECT column_name, column_default FROM information_schema.columns
  WHERE table_schema='public' AND table_name='holo_default' ORDER BY ordinal_position;
  -- The following results are returned:
  -- id,   NULL
  -- name, NULL
  
  -- Modify the default value of the id column to 0.
  ALTER TABLE IF EXISTS public.holo_default ALTER COLUMN id SET DEFAULT 0;
  
  -- Check the modified default value.
  SELECT column_name, column_default FROM information_schema.columns
  WHERE table_schema='public' AND table_name='holo_default' ORDER BY ordinal_position;
  -- The following results are returned:
  -- id,   0
  -- name, NULL
  
  -- Insert a new row to verify that the default value applies only to new data.
  INSERT INTO public.holo_default (name) VALUES ('Bob');
  SELECT * FROM public.holo_default ORDER BY name;
  -- The following results are returned:
  -- Alice, NULL   (Inserted before SET DEFAULT, so no default value is applied.)
  -- Bob,   0      (Inserted after SET DEFAULT, so the default value 0 is automatically applied.)
  
  -- Drop the default value of the id column.
  ALTER TABLE IF EXISTS public.holo_default ALTER COLUMN id DROP DEFAULT;
  
  -- Check the default value after it is dropped.
  SELECT column_name, column_default FROM information_schema.columns
  WHERE table_schema='public' AND table_name='holo_default' ORDER BY ordinal_position;
  -- The following results are returned:
  -- id,   NULL
  -- name, NULL

Modify table properties

• Modify the dictionary_encoding_columns property. This re-encodes data files and is resource-intensive. Perform 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. Only the specified columns in the call are modified. Other columns remain unchanged.
    CALL UPDATE_TABLE_PROPERTY('[<schema_name>.]<table_name>', 'dictionary_encoding_columns', '[columnName{:[on|off|auto]}[,...]]');

    Important

    In Hologres V2.0 and later, running this statement with an empty value preserves the dictionary_encoding_columns property. In earlier versions, this clears the dictionary_encoding_columns property.

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

  • Parameters

    Parameter	Description
    table_name	Name of the table. Case-sensitive. Can include the schema name.
    on	Enables dictionary encoding for the current column.
    off	Disables dictionary encoding for the current column.
    auto	Hologres automatically determines whether to apply dictionary_encoding_columns based on column value repetition rates. Higher repetition yields greater benefits. In V0.8 and earlier, all text columns used dictionary_encoding_columns by default. In V0.9 and later, this is determined automatically based on data characteristics.

  • Examples

    • Use UPDATE_TABLE_PROPERTY for an incremental modification: explicitly enable dictionary encoding for column a, set column b to auto, and keep other columns unchanged.

      -- Create a table.
      CREATE TABLE public.holo_dict_test (
       a text NOT NULL,
       b text NOT NULL,
       c text NOT NULL,
       d text
      );
      
      -- Check the dictionary_encoding_columns property before modification.
      SELECT property_value FROM hologres.hg_table_properties
      WHERE table_name='holo_dict_test' AND property_key='dictionary_encoding_columns';
      -- The following result is returned: a:auto,b:auto,c:auto,d:auto (By default, 'auto' is set for all text columns in a new table.)
      
      -- Perform an incremental modification: explicitly enable for column a, and set to auto for column b.
      CALL UPDATE_TABLE_PROPERTY('public.holo_dict_test','dictionary_encoding_columns','a:on,b:auto');
      
      -- Check the dictionary_encoding_columns property after modification.
      SELECT property_value FROM hologres.hg_table_properties
      WHERE table_name='holo_dict_test' AND property_key='dictionary_encoding_columns';
      -- The following result is returned: b:auto,c:auto,d:auto,a (Only a and b are modified as specified; c and d remain unchanged. The ':on' suffix is omitted because it is the default value. Explicitly modified columns are moved to the end of the list.)

    • Use SET_TABLE_PROPERTY for a full overwrite: explicitly disable dictionary encoding for column a. The dictionary settings for unlisted columns are reset.

      -- Create a table.
      CREATE TABLE public.holo_dict_test_2 (
       a text NOT NULL,
       b text NOT NULL,
       c text NOT NULL,
       d text
      );
      
      -- Check the dictionary_encoding_columns property before modification.
      SELECT property_value FROM hologres.hg_table_properties
      WHERE table_name='holo_dict_test_2' AND property_key='dictionary_encoding_columns';
      -- The following result is returned: a:auto,b:auto,c:auto,d:auto
      
      -- Perform a full modification: disable dictionary encoding for column a. Other columns are not included in the list.
      CALL SET_TABLE_PROPERTY('public.holo_dict_test_2','dictionary_encoding_columns','a:off');
      
      -- Check the dictionary_encoding_columns property after modification.
      SELECT property_value FROM hologres.hg_table_properties
      WHERE table_name='holo_dict_test_2' AND property_key='dictionary_encoding_columns';
      -- The following result is returned: b:auto,c:auto,d:auto (Setting 'a:off' removes column a from the list. Unlisted columns b, c, and d are reset to 'auto'.)

• Modify the bitmap_columns property.

  Starting from Hologres V0.9, you can modify the bitmap_columns property without re-creating 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. Only the specified columns in the call are modified. Other columns remain unchanged.
    CALL UPDATE_TABLE_PROPERTY('[<schema_name>.]<table_name>', 'bitmap_columns', '[columnName{:[on|off]}[,...]]');

    Important

    In Hologres V2.0 and later, running this statement with an empty value preserves the bitmap_columns property. In earlier versions, this clears the bitmap_columns property.

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

  • Parameters

    Parameter	Description
    table_name	Name of the table. Case-sensitive. Can include the schema name.
    on	Enables bitmap indexing for the current column.
    off	Disables bitmap indexing for the current column.

  • Examples

    • Use UPDATE_TABLE_PROPERTY for an incremental modification: disable bitmap indexing for column a while keeping other columns unchanged.

      -- Create a table.
      CREATE TABLE public.holo_bitmap_test (
       a text NOT NULL,
       b text NOT NULL,
       c text NOT NULL,
       d text
      );
      
      -- Check the bitmap_columns property before modification.
      SELECT property_value FROM hologres.hg_table_properties
      WHERE table_name='holo_bitmap_test' AND property_key='bitmap_columns';
      -- The following result is returned: a,b,c,d (By default, this is enabled for all columns in a new table. The ':on' suffix is omitted as it is the default.)
      
      -- Perform an incremental modification: disable the bitmap index only for column a.
      CALL UPDATE_TABLE_PROPERTY('public.holo_bitmap_test','bitmap_columns','a:off');
      
      -- Check the bitmap_columns property after modification.
      SELECT property_value FROM hologres.hg_table_properties
      WHERE table_name='holo_bitmap_test' AND property_key='bitmap_columns';
      -- The following result is returned: b,c,d (Column a is disabled and removed from the list. Columns b, c, and d remain enabled by default. The ':on' suffix is omitted as it is the default.)

    • Use SET_TABLE_PROPERTY for a full overwrite: enable bitmap indexing only for column b. Unlisted columns are removed.

      -- Create a table.
      CREATE TABLE public.holo_bitmap_test_2 (
       a text NOT NULL,
       b text NOT NULL,
       c text NOT NULL,
       d text
      );
      
      -- Check the bitmap_columns property before modification.
      SELECT property_value FROM hologres.hg_table_properties
      WHERE table_name='holo_bitmap_test_2' AND property_key='bitmap_columns';
      -- The following result is returned: a,b,c,d (By default, this is enabled for all columns in a new table. The ':on' suffix is omitted as it is the default.)
      
      -- Perform a full modification: enable only for column b. Other columns are not in the list.
      CALL SET_TABLE_PROPERTY('public.holo_bitmap_test_2','bitmap_columns','b:on');
      
      -- Check the bitmap_columns property after modification.
      SELECT property_value FROM hologres.hg_table_properties
      WHERE table_name='holo_bitmap_test_2' AND property_key='bitmap_columns';
      -- The following result is returned: b (Bitmap index settings for unlisted columns a, c, and d are cleared. The ':on' suffix is omitted as it is the default.)

• Modify the Time to Live (TTL) of a table.

  • 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	TTL of table data in seconds. Must be a positive integer no less than 86400 (one day).

    Note

    TTL countdown starts when data is written. Expired data is deleted automatically but not necessarily instantly.

  • Example

    -- Create a table.
    CREATE TABLE public.holo_ttl_test (
     id bigint,
     name text
    );
    
    -- Check the TTL property before modification.
    SELECT property_value FROM hologres.hg_table_properties
    WHERE table_name='holo_ttl_test' AND property_key='time_to_live_in_seconds';
    -- The following result is returned: 3153600000 (The default value, approximately 100 years.)
    
    -- Modify the TTL to one day (86,400 seconds).
    CALL SET_TABLE_PROPERTY('public.holo_ttl_test', 'time_to_live_in_seconds', '86400');
    
    -- Check the TTL property after modification.
    SELECT property_value FROM hologres.hg_table_properties
    WHERE table_name='holo_ttl_test' AND property_key='time_to_live_in_seconds';
    -- The following result is returned: 86400 (The TTL is now set to 1 day.)

Change table schema

Starting from Hologres V1.3, you can move a table between schemas (for example, from schema1 to schema2) without re-creating the table or importing data.

• Syntax

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

  schema_name: current schema of the table. table_name: the table to modify. new_schema: destination schema.

• Example

  Move the table named schema_demo from the public schema to the testschema schema.

  -- Create the target schema.
  CREATE SCHEMA IF NOT EXISTS testschema;
  
  -- Create a table and insert sample data.
  CREATE TABLE public.schema_demo (
   id bigint PRIMARY KEY,
   name text
  );
  INSERT INTO public.schema_demo VALUES (1, 'alice'), (2, 'bob');
  
  -- Check the schema where the table resides before the move.
  SELECT table_schema, table_name FROM information_schema.tables
  WHERE table_name='schema_demo';
  -- The following result is returned: public | schema_demo
  
  -- Move the table from the public schema to the testschema schema.
  ALTER TABLE IF EXISTS public.schema_demo SET SCHEMA testschema;
  
  -- Check the schema where the table resides after the move.
  SELECT table_schema, table_name FROM information_schema.tables
  WHERE table_name='schema_demo';
  -- The following result is returned: testschema | schema_demo
  
  -- Verify that the data is fully retained.
  SELECT * FROM testschema.schema_demo ORDER BY id;
  -- The following results are returned: 1, alice / 2, bob

Modify a table with HoloWeb

HoloWeb provides a visual editor to modify table columns and properties without SQL.

1. Go to the HoloWeb console. Connect to HoloWeb to run queries.

2. In the top menu bar of the HoloWeb page, click Metadata Management.

3. On the Metadata Management page, find your instance in the Instances Connected list and double-click the target table.

4. Modify the columns and properties on the table details page.

   

5. Click Submit in the upper-right corner.