Criar e gerenciar colunas geradas

Uma coluna gerada é sempre calculada a partir de outras colunas em uma linha. Essas colunas podem simplificar uma consulta, economizar o custo da avaliação de uma expressão no momento da consulta e ser indexadas ou usadas como chave externa. Neste artigo, descreveremos como gerenciar esse tipo de coluna no banco de dados.

Adicionar uma coluna gerada a uma nova tabela

No snippet CREATE TABLE a seguir, criamos uma tabela para armazenar informações sobre usuários. Temos colunas para FirstName e LastName e definimos uma coluna gerada para FullName, que é a concatenação de FirstName e LastName.

SQL padrão do Google

CREATE TABLE Users (
  Id STRING(20) NOT NULL,
  FirstName STRING(50),
  LastName STRING(50),
  Age INT64 NOT NULL,
  FullName STRING(100) AS (ARRAY_TO_STRING([FirstName, LastName], " ")) STORED,
) PRIMARY KEY (Id);

PostgreSQL

CREATE TABLE users (
  id VARCHAR(20) NOT NULL,
  firstname VARCHAR(50),
  lastname VARCHAR(50),
  age BIGINT NOT NULL,
  fullname VARCHAR(100) GENERATED ALWAYS AS (firstname || ' ' || lastname) STORED,
  PRIMARY KEY(id)
);

O valor de FullName é computado quando uma nova linha é inserida ou quando FirstName e/ou LastName são atualizados para uma linha existente. O valor computado é armazenado junto com outras colunas da tabela. O SQL entre parênteses é chamado de expressão de geração.

• expression pode ser qualquer expressão SQL válida que possa ser atribuída ao tipo de dados da coluna com as seguintes restrições.

  • A expressão só pode referenciar colunas na mesma tabela.

  • A expressão não pode conter subconsultas.

  • A expressão não pode conter funções não determinísticas, como PENDING_COMMIT_TIMESTAMP(), CURRENT_DATE() e CURRENT_TIMESTAMP().

  • Não é possível modificar a expressão de uma coluna gerada.

• O atributo STORED após a expressão faz com que o resultado da função seja armazenado junto com outras colunas da tabela. As atualizações subsequentes de qualquer uma das colunas referenciadas fazem com que a expressão seja reavaliada e armazenada.

• Colunas geradas sem o atributo STORED não são permitidas.

• Gravações diretas em colunas geradas não são permitidas.

• As colunas geradas não podem ser usadas como uma chave primária ou parte dela. No entanto, elas podem ser chaves de índice secundárias.

• A opção allow_commit_timestamp não é permitida em colunas geradas nem em colunas referenciadas por colunas geradas.

• Não é possível alterar o tipo de dados de uma coluna gerada nem quaisquer colunas referenciadas pela coluna gerada.

• Não é possível soltar uma coluna referenciada por uma coluna gerada.

A coluna gerada pode ser consultada como qualquer outra coluna, conforme mostrado no exemplo a seguir.

SQL padrão do Google

SELECT Id, FullName
FROM Users;

PostgreSQL

SELECT id, fullname
FROM users;

Isso é equivalente à instrução a seguir, que não usa a coluna gerada armazenada.

SQL padrão do Google

SELECT Id, ARRAY_TO_STRING([FirstName, LastName], " ") as FullName
FROM Users;

PostgreSQL

SELECT id, firstname || ' ' || lastname as fullname
FROM users;

Além de simplificar a consulta e economizar o custo da avaliação da expressão no momento da consulta, uma coluna gerada também pode ser indexada ou usada como chave externa.

Criar um índice em uma coluna gerada

Para ajudar com as pesquisas na coluna FullName gerada, podemos criar um índice secundário, conforme mostrado no snippet a seguir.

SQL padrão do Google

CREATE INDEX UsersByFullName ON Users (FullName);

PostgreSQL

CREATE INDEX UserByFullName ON users (fullname);

Adicionar uma coluna gerada a uma tabela

Usando a instrução ALTER TABLE a seguir, adicionamos uma coluna gerada à tabela Users para gerar e armazenar as iniciais do usuário.

SQL padrão do Google

ALTER TABLE Users ADD COLUMN Initials STRING(2)
AS (ARRAY_TO_STRING([SUBSTR(FirstName, 0, 1), SUBSTR(LastName, 0, 1)], "")) STORED;

PostgreSQL

ALTER TABLE users ADD COLUMN Initials VARCHAR(2)
GENERATED ALWAYS AS (SUBSTR(FirstName, 0, 1) || SUBSTR(LastName, 0, 1)) STORED;

Adicionar uma coluna gerada armazenada a uma tabela atual é uma operação de longa duração para preencher os valores da coluna. Durante o preenchimento, as colunas geradas armazenadas não podem ser lidas ou consultadas. O estado de preenchimento é refletido no INFORMATION_SCHEMA.

Criar um índice parcial usando uma coluna gerada

E se quisermos consultar apenas usuários com mais de 18 anos? Uma verificação completa da tabela seria ineficiente, por isso, usamos um índice parcial.

1. Use a instrução a seguir para adicionar outra coluna gerada que retorne a idade do usuário, caso tenha mais de 18 anos, e retorne NULL.

   SQL padrão do Google

   ALTER TABLE Users ADD COLUMN AgeAbove18 INT64
   AS (IF(Age > 18, Age, NULL)) STORED;
   

   PostgreSQL

   ALTER TABLE Users ADD COLUMN AgeAbove18 BIGINT
   GENERATED ALWAYS AS (nullif( Age , least( 18, Age) )) STORED;
   

2. Crie um índice nessa nova coluna e desative a indexação de valores NULL com a palavra-chave NULL_FILTERED no SQL padrão do Google ou o predicado IS NOT NULL no PostgreSQL. Esse índice parcial é menor e mais eficiente do que um índice normal, porque exclui todas as pessoas que tenham 18 anos ou menos.

   SQL padrão do Google

   CREATE NULL_FILTERED INDEX UsersAbove18ByAge
   ON Users (AgeAbove18);
   

   PostgreSQL

   CREATE INDEX UsersAbove18ByAge ON users (AgeAbove18)
   WHERE AgeAbove18 IS NOT NULL;
   

3. Para recuperar Id e Age de todos os usuários que têm mais de 18, execute a seguinte consulta.

   SQL padrão do Google

   SELECT Id, Age
   FROM Users@{FORCE_INDEX=UsersAbove18ByAge}
   WHERE AgeAbove18 IS NOT NULL;
   

   PostgreSQL

   SELECT Id, Age
   FROM users /*@ FORCE_INDEX = UsersAbove18ByAge */
   WHERE AgeAbove18 IS NOT NULL;
   

4. Para filtrar por uma idade diferente, por exemplo, para recuperar todos os usuários com mais de 21 anos, use o mesmo índice e filtro na coluna gerada da seguinte forma:

   SQL padrão do Google

   SELECT Id, Age
   FROM Users@{FORCE_INDEX=UsersAbove18ByAge}
   WHERE AgeAbove18 > 21;
   

   PostgreSQL

   SELECT Id, Age
   FROM users /*@ FORCE_INDEX = UsersAbove18ByAge */
   WHERE AgeAbove18 > 21;
   

Remover uma coluna gerada

A instrução DDL a seguir descarta uma coluna gerada da tabela Users.

ALTER TABLE Users
DROP COLUMN Initials;

Modificar uma expressão de coluna gerada

Não é permitido modificar a expressão de uma coluna gerada. Em vez disso, é necessário descartar a coluna existente e criar uma nova coluna gerada com a nova expressão.

Ver as propriedades de uma coluna gerada

O INFORMATION_SCHEMA do Cloud Spanner contém informações sobre as colunas geradas no banco de dados. Veja a seguir alguns exemplos das perguntas que é possível responder consultando o esquema de informações.

Quais colunas geradas são definidas no meu banco de dados?

SELECT c.TABLE_NAME, c.COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS as c
WHERE c.GENERATION_EXPRESSION IS NOT NULL;

Qual é o estado atual das colunas geradas na tabela Users?

Se você adicionou uma coluna gerada a uma tabela atual, convém visualizar o estado atual dela para determinar, por exemplo, se a coluna foi preenchida e está totalmente utilizável. Se SPANNER_STATE retornar WRITE_ONLY na consulta a seguir, isso significa que o Cloud Spanner ainda está em processo de preenchimento da coluna e nenhuma leitura é permitida.

SQL padrão do Google

SELECT c.TABLE_NAME, c.COLUMN_NAME, c.SPANNER_STATE
FROM INFORMATION_SCHEMA.COLUMNS AS c
WHERE c.TABLE_NAME="Users" AND c.GENERATION_EXPRESSION IS NOT NULL;

PostgreSQL

SELECT c.TABLE_NAME, c.COLUMN_NAME, c.SPANNER_STATE
FROM INFORMATION_SCHEMA.COLUMNS AS c
WHERE c.TABLE_NAME='users' AND c.GENERATION_EXPRESSION IS NOT NULL;

A seguir

• Saiba mais sobre o INFORMATION SCHEMA do Cloud Spanner.

• Veja mais detalhes sobre as colunas geradas nos detalhes do parâmetro CREATE TABLE.