Hologres:ALTER TABLE

Limitaciones

Notas de uso

Modificar propiedades de la tabla como dictionary_encoding_columns, bitmap_columns o time_to_live_in_seconds puede desencadenar un proceso de compactación en segundo plano. Este proceso consume recursos de CPU y podría causar un aumento temporal en el almacenamiento de la instancia, el cual vuelve a la normalidad posteriormente.

Modificar tipo de datos

En Hologres V3.0 y versiones posteriores, es posible modificar el tipo de datos de una columna en una tabla interna.

• Limitaciones

  • Se pueden modificar los tipos de columna en tablas no particionadas y tablas principales, pero no en tablas secundarias.

  • No es posible modificar el tipo de datos de una columna de partición en una tabla principal.

  • Las cláusulas COLLATE y USING no son compatibles.

  • Solo se admiten las siguientes conversiones de tipos de datos:

    Tipo de origen	Tipo de destino	Notas
    VARCHAR(N)	VARCHAR(M)	M debe ser mayor que N.
    VARCHAR(N)	TEXT	Ninguna
    CHAR(N)	CHAR(M)	M debe ser mayor que N.
    CHAR(N)	VARCHAR(M)	M debe ser mayor o igual que N.
    CHAR(N)	TEXT	Ninguna
    JSON	TEXT	Ninguna
    VARCHAR(N)[]	VARCHAR(M)[]	M debe ser mayor que N.
    VARCHAR(N)[]	TEXT[]	Ninguna

• Sintaxis

  ALTER TABLE [IF EXISTS] [<schema_name>.]<table_name> ALTER [ COLUMN ] <column_name> TYPE <data_type>;

• Ejemplo

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

Cambiar nombre de tabla

Agregar columna

Utilice la instrucción ALTER TABLE para agregar una columna a una tabla. Las nuevas columnas siempre se agregan al final de la tabla.

• Sintaxis

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

• Ejemplo

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

Eliminar columna (Beta)

En Hologres V2.0 y versiones posteriores, es posible eliminar columnas de una tabla.

• Limitaciones

  • Esta función solo está disponible en Hologres V2.0 y versiones posteriores. Si su instancia ejecuta una versión anterior a la V2.0, consulte troubleshoot common upgrade preparation errors o contáctenos para obtener asistencia. Para más información, consulte How do I get more online support?.

  • En una tabla particionada, solo se pueden eliminar columnas de la tabla principal. Esta acción elimina automáticamente la columna correspondiente de todas sus tablas secundarias. Dado que esta operación consume muchos recursos, debe realizarse fuera del horario pico.

  • Solo el propietario de la tabla puede eliminar una columna. Si su base de datos utiliza el modelo de permisos simple (SPM), debe ser miembro del grupo de usuarios de desarrolladores.

  • No es posible eliminar una columna configurada como clave principal, clave de distribución, clave de agrupamiento o especificada en la propiedad event_time_column.

  • No es posible eliminar una columna de una tabla externa.

  • Eliminar una columna relacionada con JSONB también elimina su índice asociado.

  • Eliminar una columna proxima_vector requiere el parámetro cascade.

  • Eliminar una columna SERIAL también elimina la secuencia creada para ella.

  • Si una vista materializada depende de una tabla, no es posible eliminar dicha tabla ni ninguna de sus columnas a las que haga referencia la vista.

• Sintaxis

  Importante

  Esta función no es compatible con versiones de Hologres anteriores a la 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 ]

• Ejemplo

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

  Consulte la tabla:

  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)

Cambiar nombre de columna

• Sintaxis

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

• Ejemplo

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

Modificar valor predeterminado

Utilice la instrucción ALTER TABLE para modificar el valor predeterminado de una columna. El valor predeterminado puede ser una constante o una expresión constante. Este cambio solo afecta a las operaciones INSERT y UPDATE posteriores; no altera los datos existentes. Esta función solo está disponible en Hologres V0.9.23 y versiones posteriores.

• Sintaxis

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

• Ejemplo

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

Modificar propiedades de tabla

Puede modificar las propiedades de la tabla mediante las instrucciones que se describen a continuación:

• Modifique la propiedad dictionary_encoding_columns. Cambiar la configuración de codificación de diccionario desencadena un proceso de recodificación de datos que consume CPU y memoria. Realice este cambio fuera del horario pico.

  • Sintaxis

    -- 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]}[,...]]');

    Importante

    En Hologres V2.0 y versiones posteriores, ejecutar la instrucción UPDATE_TABLE_PROPERTY con un valor vacío conserva la propiedad dictionary_encoding_columns. En versiones anteriores, la misma acción borra la propiedad dictionary_encoding_columns.

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

  • Parámetros

    Parámetro	Descripción
    table_name	Nombre de la tabla de destino. El nombre distingue entre mayúsculas y minúsculas, y puede incluir la calificación del esquema.
    on	Habilita la propiedad dictionary_encoding_columns para la columna especificada.
    off	Deshabilita la propiedad dictionary_encoding_columns para la columna especificada.
    auto	Hologres determina automáticamente si aplica dictionary_encoding_columns según la tasa de repetición de valores en la columna. Una mayor tasa de repetición ofrece mayores beneficios con la codificación de diccionario. En Hologres V0.8 y versiones anteriores, la propiedad dictionary_encoding_columns estaba habilitada de forma predeterminada para todas las columnas text. En Hologres V0.9 y versiones posteriores, la codificación de diccionario se habilita según las características de los datos.

  • Ejemplos

    • Use UPDATE_TABLE_PROPERTY para una modificación incremental: habilite explícitamente la codificación de diccionario para la columna a, configure la columna b en auto y deje las demás columnas sin cambios.

      -- 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 para una sobrescritura completa: deshabilite explícitamente la codificación de diccionario para la columna a. La configuración de diccionario de las columnas no listadas se restablece.

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

• Modifique la propiedad bitmap_columns.

  En Hologres V0.9 y versiones posteriores, es posible modificar la propiedad bitmap_columns sin necesidad de volver a crear la tabla.

  • Sintaxis

    -- 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]}[,...]]');

    Importante

    En Hologres V2.0 y versiones posteriores, ejecutar la instrucción UPDATE_TABLE_PROPERTY con un valor vacío conserva la propiedad bitmap_columns. En versiones anteriores, esto borra la propiedad bitmap_columns.

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

  • Parámetros

    Parámetro	Descripción
    table_name	Nombre de la tabla de destino. El nombre distingue entre mayúsculas y minúsculas, y puede incluir la calificación del esquema.
    on	Habilita la propiedad bitmap_columns para la columna especificada.
    off	Deshabilita la propiedad bitmap_columns para la columna especificada.

  • Ejemplos

    • Use UPDATE_TABLE_PROPERTY para una modificación incremental: deshabilite el índice de mapa de bits solo para la columna a y deje las columnas b, c y d sin cambios. De forma predeterminada, los índices de mapa de bits se habilitan automáticamente para todas las columnas en una tabla nueva.

      -- 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 para una sobrescritura completa: habilite el índice de mapa de bits solo para la columna b. Los índices de mapa de bits de las columnas no listadas a, c y d se eliminan.

      -- 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.)

• Modifique el tiempo de vida (TTL) de una tabla.

  • Sintaxis

    call set_table_property('[<schema_name>.]<table_name>', 'time_to_live_in_seconds', '<non_negative_literal>');

  • Parámetros

    Parámetro	Descripción
    time_to_live_in_seconds	Tiempo de vida (TTL) de los datos de la tabla, en segundos. El valor debe ser un entero mayor o igual a 86,400 (un día).

    Nota

    La eliminación de datos caducados es automática, pero puede sufrir retrasos.

  • Ejemplo

    -- 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.)

Cambiar esquema de tabla

A partir de Hologres V1.3, es posible mover una tabla entre esquemas, por ejemplo, de schema1 a schema2, sin volver a crear la tabla ni importar datos. Esto permite cambiar rápidamente el esquema de una tabla.

• Sintaxis

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

  schema_name: esquema actual de la tabla. table_name: nombre de la tabla que se va a mover. new_schema: esquema de destino.

• Ejemplo

  Mueva la tabla llamada schema_demo del esquema public al esquema testschema.

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

Modificar tablas en HoloWeb

También puede usar el editor visual de HoloWeb para modificar columnas y propiedades de tablas sin escribir SQL. Para ello, siga estos pasos:

1. Vaya a la consola de HoloWeb. Para más información, consulte Connect to HoloWeb and run SQL queries.

2. En la barra de menú superior de la página de HoloWeb, haga clic en Metadata Management.

3. En la página Metadata Management, busque su instancia en la lista Instances Connected de la izquierda y haga doble clic en la tabla de destino.

4. En la página de detalles de la tabla, puede modificar visualmente las columnas y algunas propiedades de la tabla.

   En la pestaña Columns, puede consultar toda la información de las columnas. Para agregar una columna, haga clic en Add Column en la parte inferior y luego introduzca un nombre y un tipo de datos en la nueva fila que aparece.

5. Haga clic en Submit en la esquina superior derecha para aplicar los cambios.