Hologres:generated column

Cenários

• Cálculo automático de campos obrigatórios: elimina a necessidade de processar manualmente a lógica de cálculo.

• Consistência dos dados: evita inconsistências causadas por erros humanos ou problemas na lógica do código.

• Otimização do desempenho de consultas: em cenários de consulta de alta frequência, ler colunas geradas armazenadas equivale a ler colunas comuns.

• Simplificação da lógica de negócios: reduz a complexidade do SQL em operações fixas e comuns de transformação de dados.

Usar colunas geradas adequadamente, conforme as necessidades do seu negócio, melhora significativamente a eficiência do desenvolvimento e garante a confiabilidade dos dados.

Sintaxe

Use a cláusula GENERATED ALWAYS AS para declarar uma coluna gerada e a palavra-chave STORED para especificar uma coluna gerada armazenada.

• Crie uma tabela com uma coluna gerada.

  CREATE TABLE generated_col_t (
      [...,]
      col1 INT,
      col2 INT GENERATED ALWAYS AS (col1 + 1) STORED
  );

• Crie uma tabela particionada lógica que contenha uma coluna gerada e use-a como chave de partição.

  CREATE TABLE generated_col_logical_part (
      a TEXT,
      b INT,
      ts TIMESTAMP NOT NULL,
      d TIMESTAMP GENERATED ALWAYS AS (date_trunc('day', ts)) STORED NOT NULL
  )
  LOGICAL PARTITION BY LIST(d);

Precauções

• CREATE TABLE

  • Ao definir uma coluna gerada, use apenas funções ou expressões IMMUTABLE. Funções não IMMUTABLE, como CURRENT_DATE e RANDOM, não são aceitas.

  • A expressão de uma coluna gerada não pode referenciar outra coluna gerada. Também não é permitido definir um valor default para uma coluna gerada.

  • Ao criar uma tabela particionada, você pode definir a chave de partição de uma logical partitioned table como uma coluna gerada, mas não pode definir a chave de partição de uma physical partitioned table como uma coluna gerada. Colunas comuns em tabelas particionadas podem ser configuradas como colunas geradas.

  • O comando CREATE FOREIGN TABLE não oferece suporte a colunas geradas ao criar uma tabela externa.

  • Colunas geradas podem atuar como diversos índices do Hologres, incluindo chaves primárias, chaves de distribuição, chaves de segmento, índices clusterizados, índices bitmap e colunas codificadas por dicionário.

• ALTER TABLE

  • Não é possível adicionar uma coluna como coluna gerada.

  • É possível remover uma coluna gerada. No entanto, não é permitido remover as colunas referenciadas por ela antes de removê-la.

  • Não é permitido modificar o tipo de dados de uma coluna gerada nem os tipos de dados das colunas que ela referencia. Recomendamos usar o recurso REBUILD para essa finalidade. Para mais informações, consulte REBUILD.

  • É possível renomear uma coluna gerada.

• DML/DQL

  • Ao inserir ou atualizar dados, omita a coluna gerada na instrução ou especifique seu valor como default. Não é possível inserir diretamente um valor específico em uma coluna gerada.

  • Durante a atualização de dados, se uma coluna gerada ou sua coluna referenciada for uma chave de distribuição, o sistema não oferecerá suporte à atualização dessa coluna.

  • Para executar atualizações usando um plano fixo, caso a chave primária inclua uma coluna gerada, ela também deverá conter todas as colunas referenciadas por essa coluna gerada.

  • Ao realizar atualizações parciais de colunas com um plano fixo, se uma coluna gerada referenciar várias colunas comuns, o sistema não oferecerá suporte à atualização de apenas algumas dessas colunas.

  • Todas as demais operações em tabelas com colunas geradas têm suporte, incluindo leituras e gravações executadas pelo mecanismo HQE, operações de leitura e escrita via planos fixos e comandos como Copy.

• Outras operações

  • O comando CREATE TABLE LIKE aceita tabelas que contêm colunas geradas. Ative o parâmetro hg_experimental_enable_create_table_like_properties para preservar as propriedades das colunas geradas.

  • O uso de CREATE TABLE AS não tem suporte quando as tabelas de origem possuem colunas geradas.

  • Para modificar parâmetros de uma tabela com colunas geradas, utilize a sintaxe REBUILD (incluindo a migração do grupo de tabelas). Para mais informações, consulte REBUILD. Não há suporte para migrar o grupo de tabelas usando a sintaxe HG_MOVE_TABLE_TO_TABLE_GROUP.

  • Para executar uma operação INSERT OVERWRITE em uma tabela com colunas geradas, use a sintaxe nativa INSERT OVERWRITE disponível no Hologres V3.1 e versões posteriores. A sintaxe legada hg_insert_overwrite não tem suporte. Para mais informações, consulte INSERT OVERWRITE.

Exemplos

1. Crie uma tabela com uma coluna gerada.

   CREATE TABLE generated_col_t (
       id INT PRIMARY KEY,
       col1 INT,
       col2 INT GENERATED ALWAYS AS (col1 + 1) STORED
   );

2. Importe dados.

   • Importe dados para todas as colunas não geradas. Exemplo:

     INSERT INTO generated_col_t VALUES (1, 1);
     INSERT INTO generated_col_t(id, col1) VALUES (2, 2);

     A consulta SELECT * FROM generated_col_t; retorna o seguinte resultado:

     id	col1	col2
     1	1	2
     2	2	3

   • Use a palavra-chave default para a coluna gerada durante a importação de dados. Por exemplo:

     INSERT INTO generated_col_t VALUES (3, 3, default);
     INSERT INTO generated_col_t(id, col1, col2) VALUES (4, 4, default);

     A consulta SELECT * FROM generated_col_t; retorna o seguinte resultado:

     id	col1	col2
     4	4	5
     2	2	3
     3	3	4
     1	1	2

   • Sem suporte: importar dados diretamente para colunas geradas. Exemplo:

     INSERT INTO generated_col_t VALUES (5, 5, 6);
     INSERT INTO generated_col_t(id, col1, col2) VALUES (6, 6, 7);

     O sistema retorna o seguinte resultado.

     ERROR: cannot insert into column "col2"
     Detail: Column "col2" is a generated column.

3. Atualize dados.

   • Atualize colunas não geradas. Exemplo:

     UPDATE generated_col_t SET col1 = 2 WHERE id = 1;

     A consulta SELECT * FROM generated_col_t; retorna o seguinte resultado:

     id	col1	col2
     2	2	3
     3	3	4
     4	4	5
     1	2	3     -- This row has been changed 

   • Use a palavra-chave default para a coluna gerada durante uma atualização. Por exemplo:

     UPDATE generated_col_t SET col1 = 3, col2 = default WHERE id = 2;

     A consulta SELECT * FROM generated_col_t; retorna o seguinte resultado:

     id	col1	col2
     3	3	4
     2	3	4    -- This row has been changed
     4	4	5
     1	2	3

   • Sem suporte: atualizar diretamente colunas geradas. Exemplo:

     UPDATE generated_col_t SET col2 = 4 WHERE id = 3;

     O sistema retorna o seguinte resultado.

     ERROR: column "col2" can only be updated to DEFAULT
     Detail: Column "col2" is a generated column.

Use a seguinte consulta SQL para verificar se uma função é IMMUTABLE para tipos de parâmetros específicos. Por exemplo, a função to_char é IMMUTABLE apenas quando sua entrada é do tipo TIMESTAMP WITH TIME ZONE. Portanto, ao usar essa função em uma coluna gerada, certifique-se de que o tipo do parâmetro corresponda.

SELECT n.nspname AS "Schema",
  p.proname AS "Name",
  pg_catalog.pg_get_function_result(p.oid) AS "Result data type",
  pg_catalog.pg_get_function_arguments(p.oid) AS "Argument data types",
 CASE p.prokind
  WHEN 'a' THEN 'agg'
  WHEN 'w' THEN 'window'
  WHEN 'p' THEN 'proc'
  ELSE 'func'
 END AS "Type",
 CASE
  WHEN p.provolatile = 'i' THEN 'immutable'
  WHEN p.provolatile = 's' THEN 'stable'
  WHEN p.provolatile = 'v' THEN 'volatile'
 END AS "Volatility",
 CASE
  WHEN p.proparallel = 'r' THEN 'restricted'
  WHEN p.proparallel = 's' THEN 'safe'
  WHEN p.proparallel = 'u' THEN 'unsafe'
 END AS "Parallel",
 pg_catalog.pg_get_userbyid(p.proowner) AS "Owner",
 CASE WHEN prosecdef THEN 'definer' ELSE 'invoker' END AS "Security",
 pg_catalog.array_to_string(p.proacl, E'\n') AS "Access privileges",
 l.lanname AS "Language",
 p.prosrc AS "Source code",
 pg_catalog.obj_description(p.oid, 'pg_proc') AS "Description"
FROM pg_catalog.pg_proc p
     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace
     LEFT JOIN pg_catalog.pg_language l ON l.oid = p.prolang
-- Target function
WHERE p.proname OPERATOR(pg_catalog.~) '^(to_char)$' COLLATE pg_catalog.default
  AND pg_catalog.pg_function_is_visible(p.oid)
ORDER BY 1, 2, 4;