Hologres:ALTER TABLE

Limitações

Observações de uso

Modificar propriedades da tabela, como dictionary_encoding_columns, bitmap_columns ou time_to_live_in_seconds, pode acionar um processo de compactação em segundo plano. Esse processo consome recursos de CPU e pode causar um aumento temporário no armazenamento da instância, que posteriormente retorna ao normal.

Modificar tipo de dados

No Hologres V3.0 e versões posteriores, é possível modificar o tipo de dados de uma coluna em uma tabela interna.

• Limitações

  • É possível modificar tipos de colunas em tabelas não particionadas e tabelas pai, mas não em tabelas filhas.

  • Não é permitido alterar o tipo de dados de uma coluna de partição em uma tabela pai.

  • As cláusulas COLLATE e USING não são suportadas.

  • Somente as seguintes conversões de tipo de dados são suportadas:

    Tipo de origem	Tipo de destino	Observações
    VARCHAR(N)	VARCHAR(M)	M deve ser maior que N.
    VARCHAR(N)	TEXT	Nenhuma
    CHAR(N)	CHAR(M)	M deve ser maior que N.
    CHAR(N)	VARCHAR(M)	M deve ser maior ou igual a N.
    CHAR(N)	TEXT	Nenhuma
    JSON	TEXT	Nenhuma
    VARCHAR(N)[]	VARCHAR(M)[]	M deve ser maior que N.
    VARCHAR(N)[]	TEXT[]	Nenhuma

• Sintaxe

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

• Exemplo

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

Renomear tabela

Adicionar coluna

A instrução ALTER TABLE permite adicionar uma coluna a uma tabela. Novas colunas são sempre inseridas ao final da estrutura da tabela.

• Sintaxe

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

• Exemplo

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

Remover coluna (Beta)

A partir do Hologres V2.0, é possível remover colunas de uma tabela.

• Limitações

  • Este recurso está disponível apenas no Hologres V2.0 e versões posteriores. Se sua instância executar uma versão anterior à V2.0, consulte troubleshoot common upgrade preparation errors ou entre em contato conosco para obter assistência. Para mais informações, consulte How do I get more online support?.

  • Em tabelas particionadas, você só pode remover colunas da tabela pai. Essa ação remove automaticamente a coluna correspondente de todas as tabelas filhas. Como essa operação consome muitos recursos, execute-a fora dos horários de pico.

  • Apenas o proprietário da tabela tem permissão para remover uma coluna. Caso seu banco de dados utilize o Modelo de Permissão Simples (SPM), você deve ser membro do grupo de usuários desenvolvedores.

  • Não é possível remover uma coluna definida como chave primária, chave de distribuição, chave de clustering ou especificada na propriedade event_time_column.

  • Não há suporte para remoção de colunas em tabelas externas.

  • Remover uma coluna relacionada a JSONB também elimina seu índice associado.

  • Para remover uma coluna proxima_vector, use o parâmetro cascade.

  • Excluir uma coluna SERIAL remove também a sequence criada para ela.

  • Se uma visualização materializada depender de uma tabela, não será possível excluir essa tabela nem quaisquer colunas referenciadas pela visualização.

• Sintaxe

  Importante

  Este recurso não é compatível com versões do Hologres anteriores à 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 ]

• Exemplo

  -- 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 a tabela:

  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)

Renomear coluna

• Sintaxe

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

• Exemplo

  -- 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 padrão

Use a instrução ALTER TABLE para alterar o valor padrão de uma coluna. O valor padrão pode ser uma constante ou uma expressão constante. Essa mudança afeta apenas operações INSERT e UPDATE subsequentes, sem alterar os dados existentes. Este recurso está disponível somente no Hologres V0.9.23 e versões posteriores.

• Sintaxe

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

• Exemplo

  -- 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 propriedades da tabela

Use as instruções descritas abaixo para modificar as propriedades da tabela:

• Modifique a propriedade dictionary_encoding_columns. Ajustar as configurações de codificação de dicionário aciona um processo de recodificação de dados que consome CPU e memória. Realize essa alteração fora dos horários de pico.

  • Sintaxe

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

    No Hologres V2.0 e versões posteriores, executar a instrução UPDATE_TABLE_PROPERTY com um valor vazio preserva a propriedade dictionary_encoding_columns. Em versões anteriores, a mesma ação limpa a propriedade dictionary_encoding_columns.

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

  • Parâmetros

    Parâmetro	Descrição
    table_name	Nome da tabela de destino. O nome diferencia maiúsculas de minúsculas e pode ser qualificado com schema.
    on	Ativa a propriedade dictionary_encoding_columns para a coluna especificada.
    off	Desativa a propriedade dictionary_encoding_columns para a coluna especificada.
    auto	O Hologres determina automaticamente se deve aplicar dictionary_encoding_columns com base na taxa de repetição dos valores na coluna. Uma taxa de repetição maior proporciona maiores benefícios com a codificação de dicionário. No Hologres V0.8 e anteriores, a propriedade dictionary_encoding_columns era ativada por padrão para todas as colunas text. A partir do Hologres V0.9, a codificação de dicionário é habilitada conforme as características dos dados.

  • Exemplos

    • Use UPDATE_TABLE_PROPERTY para uma modificação incremental: ative explicitamente a codificação de dicionário para a coluna a, defina a coluna b como auto e mantenha as demais colunas inalteradas.

      -- 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 uma substituição completa: desative explicitamente a codificação de dicionário para a coluna a. As configurações de dicionário das colunas não listadas serão redefinidas.

      -- 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 a propriedade bitmap_columns.

  No Hologres V0.9 e versões posteriores, você pode modificar a propriedade bitmap_columns sem precisar recriar a tabela.

  • Sintaxe

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

    No Hologres V2.0 e versões posteriores, executar a instrução UPDATE_TABLE_PROPERTY com um valor vazio preserva a propriedade bitmap_columns. Em versões anteriores, isso limpa a propriedade bitmap_columns.

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

  • Parâmetros

    Parâmetro	Descrição
    table_name	Nome da tabela de destino. O nome diferencia maiúsculas de minúsculas e pode ser qualificado com schema.
    on	Ativa a propriedade bitmap_columns para a coluna especificada.
    off	Desativa a propriedade bitmap_columns para a coluna especificada.

  • Exemplos

    • Use UPDATE_TABLE_PROPERTY para uma modificação incremental: desative o índice bitmap apenas para a coluna a e mantenha as colunas b, c e d inalteradas. Por padrão, índices bitmap são ativados automaticamente para todas as colunas em uma nova tabela.

      -- 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 uma substituição completa: ative o índice bitmap somente para a coluna b. Os índices bitmap das colunas não listadas a, c e d serão removidos.

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

• Altere o Time to Live (TTL) de uma tabela.

  • Sintaxe

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

  • Parâmetros

    Parâmetro	Descrição
    time_to_live_in_seconds	Tempo de vida (TTL) dos dados da tabela, em segundos. O valor deve ser um número inteiro maior ou igual a 86.400 (um dia).

    Nota

    A exclusão de dados expirados é automática, mas pode sofrer atrasos.

  • Exemplo

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

Alterar schema da tabela

A partir do Hologres V1.3, é possível mover uma tabela entre schemas, por exemplo, de schema1 para schema2, sem precisar recriar a tabela ou importar dados. Isso permite alterar rapidamente o schema de uma tabela.

• Sintaxe

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

  schema_name: o schema atual da tabela. table_name: o nome da tabela a ser movida. new_schema: o schema de destino.

• Exemplo

  Mova a tabela chamada schema_demo do schema public para o schema 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 tabelas no HoloWeb

Você também pode usar o editor visual do HoloWeb para modificar colunas e propriedades da tabela sem escrever SQL. Siga estas etapas:

1. Acesse o console do HoloWeb. Para mais informações, consulte Connect to HoloWeb and run SQL queries.

2. Na barra de menu superior da página do HoloWeb, clique em Metadata Management.

3. Na página Metadata Management, localize sua instância na lista Instances Connected à esquerda e dê um clique duplo na tabela desejada.

4. Na página de detalhes da tabela, modifique visualmente as colunas e algumas propriedades da tabela.

   Na aba Columns, visualize todas as informações das colunas. Para adicionar uma coluna, clique em Add Column na parte inferior e insira um nome e tipo de dados na nova linha exibida.

5. Clique em Submit no canto superior direito para aplicar as alterações.