ALTER TABLE

Limitations

Usage notes

Modifying table properties such as dictionary_encoding_columns, bitmap_columns, or time_to_live_in_seconds can trigger a background compaction process. This process consumes CPU resources and may cause a temporary increase in instance storage, which later returns to normal.

Modify data type

In Hologres V3.0 and later, you can modify the data type of a column in an internal table.

• Limitations

  • You can modify column types in non-partitioned tables and parent tables, but not in child tables.

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

You can use the ALTER TABLE statement to add a column to a table. New columns are always added to the end of the 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>; 

• 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 column structure before adding a new 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 column structure 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 column structure 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)

In Hologres V2.0 and later, you can drop columns 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 more information, see How do I get more online support?.

  • For a partitioned table, you can drop columns only from the parent table. This action automatically drops the corresponding column from all its child tables. This operation is resource-intensive and should be performed 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 specified in the event_time_column property.

  • You cannot drop a column from a foreign table.

  • Dropping a JSONB-related column also drops its associated index.

  • Dropping a proxima_vector column requires the cascade parameter.

  • Dropping a SERIAL column also drops the sequence created for it.

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

• Syntax

  Important

  This feature is 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

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. This change affects only subsequent INSERT and UPDATE operations; it does not alter existing data. This feature is available only 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.
  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

You can modify table properties by using the statements described below:

• Modify the dictionary_encoding_columns property. Modifying dictionary encoding settings triggers a data re-encoding process that consumes CPU and memory. 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. Only the specified columns 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 the UPDATE_TABLE_PROPERTY statement with an empty value preserves the dictionary_encoding_columns property. In earlier versions, the same action clears the dictionary_encoding_columns property.

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

  • Parameters

    Parameter	Description
    table_name	The name of the target table. The name is case-sensitive and can be schema-qualified.
    on	Enables the dictionary_encoding_columns property for the specified column.
    off	Disables the dictionary_encoding_columns property for the specified column.
    auto	Hologres automatically determines whether to apply dictionary_encoding_columns based on the repetition rate of values in the column. A higher repetition rate provides greater benefits from dictionary encoding. In Hologres V0.8 and earlier, the dictionary_encoding_columns property was enabled for all text columns by default. In Hologres V0.9 and later, dictionary encoding is enabled 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 leave 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 only 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.

  In Hologres V0.9 and later, 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 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 the UPDATE_TABLE_PROPERTY 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	The name of the target table. The name is case-sensitive and can be schema-qualified.
    on	Enables the bitmap_columns property for the specified column.
    off	Disables the bitmap_columns property for the specified column.

  • Examples

    • Use UPDATE_TABLE_PROPERTY for an incremental modification: disable the bitmap index only for column a, and leave columns b, c, and d unchanged. By default, bitmap indexes are automatically enabled for all columns in a new table.

      -- 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 because 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 because it is the default.)

    • Use SET_TABLE_PROPERTY for a full overwrite: enable the bitmap index only for column b. The bitmap indexes for the unlisted columns a, c, and d 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 because 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 because 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	The Time to Live (TTL) of table data, in seconds. The value must be an integer greater than or equal to 86,400 (one day).

    Note

    Deletion of expired data is automatic but may be delayed.

  • 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. This allows you to quickly change a table's schema.

• Syntax

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

  schema_name: the table's current schema. table_name: the name of the table to move. new_schema: the 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 result is returned: 1, alice / 2, bob

Modify tables in HoloWeb

You can also use the HoloWeb visual editor to modify table columns and properties without writing SQL. To do so, follow these steps:

1. Go to the HoloWeb console. For more information, see Connect to HoloWeb and run SQL 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 on the left and double-click the target table.

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

   On the Columns tab, you can view all column information. To add a column, click Add Column at the bottom, and then enter a name and data type in the new row that appears.

5. Click Submit in the upper-right corner to apply your changes.