Hologres:ALTER TABLE

Limitations

Notes d'utilisation

La modification des propriétés de table telles que dictionary_encoding_columns, bitmap_columns ou time_to_live_in_seconds peut déclencher un processus de compactage en arrière-plan. Ce processus consomme des ressources CPU et peut entraîner une augmentation temporaire du stockage de l'instance, qui revient ensuite à la normale.

Modifier le type de données

Dans Hologres V3.0 et versions ultérieures, vous pouvez modifier le type de données d'une colonne dans une table interne.

• Limitations

  • Vous pouvez modifier les types de colonnes dans les tables non partitionnées et les tables parentes, mais pas dans les tables enfants.

  • Vous ne pouvez pas modifier le type de données d'une colonne de partition dans une table parente.

  • Les clauses COLLATE et USING ne sont pas prises en charge.

  • Seules les conversions de types de données suivantes sont prises en charge :

    Type source	Type cible	Notes
    VARCHAR(N)	VARCHAR(M)	M doit être supérieur à N.
    VARCHAR(N)	TEXT	Aucune
    CHAR(N)	CHAR(M)	M doit être supérieur à N.
    CHAR(N)	VARCHAR(M)	M doit être supérieur ou égal à N.
    CHAR(N)	TEXT	Aucune
    JSON	TEXT	Aucune
    VARCHAR(N)[]	VARCHAR(M)[]	M doit être supérieur à N.
    VARCHAR(N)[]	TEXT[]	Aucune

• Syntaxe

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

• Exemple

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

Renommer une table

Ajouter une colonne

Vous pouvez utiliser l'instruction ALTER TABLE pour ajouter une colonne à une table. Les nouvelles colonnes sont toujours ajoutées à la fin de la table.

• Syntaxe

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

• Exemple

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

Supprimer une colonne (Bêta)

Dans Hologres V2.0 et versions ultérieures, vous pouvez supprimer des colonnes d'une table.

• Limitations

  • Cette fonctionnalité est disponible uniquement dans Hologres V2.0 et versions ultérieures. Si votre instance exécute une version antérieure à la V2.0, reportez-vous à résolution des erreurs courantes de préparation à la mise à niveau ou contactez-nous pour obtenir de l'aide. Pour plus d'informations, consultez Comment obtenir davantage d'assistance en ligne ?.

  • Pour une table partitionnée, vous ne pouvez supprimer des colonnes que depuis la table parente. Cette action supprime automatiquement la colonne correspondante de toutes ses tables enfants. Cette opération consomme beaucoup de ressources et doit être effectuée pendant les heures creuses.

  • Seul le propriétaire de la table peut supprimer une colonne. Si votre base de données utilise le modèle d'autorisation simple (SPM), vous devez être membre du groupe d'utilisateurs développeurs.

  • Vous ne pouvez pas supprimer une colonne définie comme clé primaire, clé de distribution, clé de clustering ou spécifiée dans la propriété event_time_column.

  • Vous ne pouvez pas supprimer une colonne d'une table externe.

  • La suppression d'une colonne liée à JSONB supprime également son index associé.

  • La suppression d'une colonne proxima_vector nécessite le paramètre cascade.

  • La suppression d'une colonne SERIAL supprime également la séquence créée pour celle-ci.

  • Si une vue matérialisée dépend d'une table, vous ne pouvez pas supprimer cette table ni aucune de ses colonnes référencées par la vue.

• Syntaxe

  Important

  Cette fonctionnalité n'est pas prise en charge dans les versions de Hologres antérieures à 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 ]

• Exemple

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

  Interrogez la 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)

Renommer une colonne

• Syntaxe

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

• Exemple

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

Modifier la valeur par défaut

L'instruction ALTER TABLE permet de modifier la valeur par défaut d'une colonne. Cette valeur peut être une constante ou une expression constante. Ce changement n'affecte que les opérations INSERT et UPDATE ultérieures ; les données existantes ne sont pas modifiées. Cette fonctionnalité est disponible uniquement à partir de Hologres V0.9.23.

• Syntaxe

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

• Exemple

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

Modifier les propriétés de table

Vous pouvez modifier les propriétés de table à l'aide des instructions décrites ci-dessous :

• Modifiez la propriété dictionary_encoding_columns. La modification des paramètres d'encodage par dictionnaire déclenche un processus de ré-encodage des données qui consomme du CPU et de la mémoire. Effectuez cette opération pendant les heures creuses.

  • Syntaxe

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

    À partir de Hologres V2.0, l'exécution de l'instruction UPDATE_TABLE_PROPERTY avec une valeur vide conserve la propriété dictionary_encoding_columns. Dans les versions antérieures, la même action efface la propriété dictionary_encoding_columns.

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

  • Paramètres

    Parameter	Description
    table_name	Nom de la table cible. Le nom respecte la casse et peut être qualifié par le schéma.
    on	Active la propriété dictionary_encoding_columns pour la colonne spécifiée.
    off	Désactive la propriété dictionary_encoding_columns pour la colonne spécifiée.
    auto	Hologres détermine automatiquement s'il convient d'appliquer dictionary_encoding_columns en fonction du taux de répétition des valeurs dans la colonne. Un taux de répétition plus élevé accroît les avantages de l'encodage par dictionnaire. Dans Hologres V0.8 et versions antérieures, la propriété dictionary_encoding_columns était activée par défaut pour toutes les colonnes text. À partir de Hologres V0.9, l'encodage par dictionnaire est activé selon les caractéristiques des données.

  • Exemples

    • Utilisez UPDATE_TABLE_PROPERTY pour une modification incrémentielle : activez explicitement l'encodage par dictionnaire pour la colonne a, définissez la colonne b sur auto et laissez les autres colonnes inchangées.

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

    • Utilisez SET_TABLE_PROPERTY pour un remplacement complet : désactivez explicitement l'encodage par dictionnaire pour la colonne a. Les paramètres de dictionnaire des colonnes non répertoriées sont réinitialisés.

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

• Modifiez la propriété bitmap_columns.

  À partir de Hologres V0.9, vous pouvez modifier la propriété bitmap_columns sans recréer la table.

  • Syntaxe

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

    À partir de Hologres V2.0, l'exécution de l'instruction UPDATE_TABLE_PROPERTY avec une valeur vide conserve la propriété bitmap_columns. Dans les versions antérieures, cela efface la propriété bitmap_columns.

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

  • Paramètres

    Parameter	Description
    table_name	Nom de la table cible. Le nom respecte la casse et peut être qualifié par le schéma.
    on	Active la propriété bitmap_columns pour la colonne spécifiée.
    off	Désactive la propriété bitmap_columns pour la colonne spécifiée.

  • Exemples

    • Utilisez UPDATE_TABLE_PROPERTY pour une modification incrémentielle : désactivez l'index bitmap uniquement pour la colonne a, et laissez les colonnes b, c et d inchangées. Par défaut, les index bitmap sont automatiquement activés pour toutes les colonnes d'une nouvelle 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.)

    • Utilisez SET_TABLE_PROPERTY pour un remplacement complet : activez l'index bitmap uniquement pour la colonne b. Les index bitmap des colonnes non répertoriées a, c et d sont supprimés.

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

• Modifiez la durée de vie (TTL) d'une table.

  • Syntaxe

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

  • Paramètres

    Parameter	Description
    time_to_live_in_seconds	Durée de vie (TTL) des données de la table, exprimée en secondes. La valeur doit être un entier supérieur ou égal à 86 400 (un jour).

    Remarque

    La suppression des données expirées est automatique, mais peut subir un délai.

  • Exemple

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

Modifier le schéma d'une table

À partir de Hologres V1.3, vous pouvez déplacer une table d'un schéma à un autre, par exemple de schema1 vers schema2, sans avoir à recréer la table ni à importer les données. Cette fonctionnalité permet de modifier rapidement le schéma associé à une table.

• Syntaxe

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

  schema_name : schéma actuel de la table. table_name : nom de la table à déplacer. new_schema : schéma de destination.

• Exemple

  Déplacez la table nommée schema_demo du schéma public vers le schéma 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

Modifier des tables dans HoloWeb

Il est également possible de modifier les colonnes et les propriétés d'une table sans écrire de code SQL, en utilisant l'éditeur visuel de HoloWeb. Pour ce faire, procédez comme suit :

1. Accédez à la console HoloWeb. Pour plus d'informations, consultez la rubrique Connexion à HoloWeb et exécution de requêtes SQL.

2. Dans la barre de menu supérieure de la page HoloWeb, cliquez sur Metadata Management.

3. Sur la page Metadata Management, repérez votre instance dans la liste Instances Connected située à gauche, puis double-cliquez sur la table cible.

4. Sur la page des détails de la table, modifiez visuellement les colonnes et certaines propriétés.

   Sous l'onglet Columns, vous pouvez consulter toutes les informations relatives aux colonnes. Pour ajouter une colonne, cliquez sur Add Column en bas de la page, puis saisissez un nom et un type de données dans la nouvelle ligne qui s'affiche.

5. Cliquez sur Submit dans le coin supérieur droit pour appliquer vos modifications.