Crear y administrar columnas generadas

Organiza tus páginas con colecciones Guarda y categoriza el contenido según tus preferencias.

Una columna generada es una columna que siempre se calcula a partir de otras columnas en una fila. Estas columnas pueden simplificar una consulta, ahorrar el costo de evaluar una expresión en el momento de la consulta y pueden indexarse o usarse como una clave externa. En este artículo, describiremos cómo administrar este tipo de columna en tu base de datos.

Agregar una columna generada a una tabla nueva

En el siguiente fragmento de CREATE TABLE, creamos una tabla para almacenar información sobre los usuarios. Tenemos columnas para FirstName y LastName, y definimos una columna generada para FullName, que es la concatenación de FirstName y LastName.

SQL estándar de 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)
);

El valor de FullName se calcula cuando se inserta una fila nueva o cuando se actualizan FirstName o LastName para una fila existente. El valor calculado se almacena junto con otras columnas de la tabla. El SQL entre paréntesis se denomina expresión de generación.

  • expression puede ser cualquier expresión de SQL válida que se pueda asignar al tipo de datos de la columna con las siguientes restricciones.

  • El atributo STORED que sigue a la expresión hace que el resultado de la función se almacene junto con otras columnas de la tabla. Las actualizaciones posteriores en cualquiera de las columnas referenciadas hacen que la expresión se vuelva a evaluar y almacene.

  • No se permiten columnas generadas sin el atributo STORED.

  • No se permite la escritura directa en columnas generadas.

  • Las columnas generadas no se pueden usar como clave primaria ni como parte de ella. Sin embargo, pueden ser claves de índice secundarias.

  • La opción de columna allow_commit_timestamp no está permitida en las columnas generadas ni en las columnas a las que hacen referencia las columnas generadas.

  • No puedes cambiar el tipo de datos de una columna generada ni ninguna columna a la que haga referencia la columna generada.

  • No puedes descartar una columna a la que hace referencia una columna generada.

La columna generada se puede consultar como cualquier otra columna, como se muestra en el siguiente ejemplo.

SQL estándar de Google

SELECT Id, FullName
FROM Users;

PostgreSQL

SELECT id, fullname
FROM users;

Esto equivale a la siguiente declaración, que no usa la columna generada generada.

SQL estándar de Google

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

PostgreSQL

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

Además de simplificar la consulta y ahorrar el costo de evaluar la expresión en el momento de la consulta, una columna generada también se puede indexar o usar como una clave externa.

Crea un índice en una columna generada

Para ayudar con las búsquedas en nuestra columna FullName generada, podemos crear un índice secundario como se muestra en el siguiente fragmento.

SQL estándar de Google

CREATE INDEX UsersByFullName ON Users (FullName);

PostgreSQL

CREATE INDEX UserByFullName ON users (fullname);

Agregar una columna generada a una tabla existente

Con la siguiente declaración ALTER TABLE, agregamos una columna generada a la tabla Users para generar y almacenar las iniciales del usuario.

SQL estándar de 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;

Agregar una columna generada almacenada a una tabla existente es una operación de larga duración para reabastecer los valores de la columna. Durante el reabastecimiento, las columnas generadas almacenadas no se pueden leer ni consultar. El estado de reabastecimiento se refleja en el INFORMATION_SCHEMA.

Crea un índice parcial con una columna generada

¿Y si solo queremos consultar a usuarios mayores de 18 años? Un análisis completo de la tabla sería ineficiente, por lo que usamos un índice parcial.

  1. Usa la siguiente declaración para agregar otra columna generada que muestre la edad del usuario si es mayor de 18 años y, de lo contrario, mostrará NULL.

    SQL estándar de 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. Crea un índice en esta columna nueva y, luego, inhabilita la indexación de los valores NULL con la palabra clave NULL_FILTERED en SQL estándar de Google o el predicado IS NOT NULL en PostgreSQL. Este índice parcial es más pequeño y eficiente que un índice normal porque excluye a todas las personas que son mayores de 18 años.

    SQL estándar de Google

    CREATE NULL_FILTERED INDEX UsersAbove18ByAge
    ON Users (AgeAbove18);
    

    PostgreSQL

    CREATE INDEX UsersAbove18ByAge ON users (AgeAbove18)
    WHERE AgeAbove18 IS NOT NULL;
    
  3. Para recuperar el Id y el Age de todos los usuarios mayores de 18 años, ejecuta la siguiente consulta.

    SQL estándar de 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 con una edad diferente, por ejemplo, a fin de recuperar todos los usuarios mayores de 21 años, usa el mismo índice y filtra en la columna generada de la siguiente manera:

    SQL estándar de Google

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

    PostgreSQL

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

Quitar una columna generada

La siguiente declaración DDL descarta una columna generada de la tabla Users.

ALTER TABLE Users
DROP COLUMN Initials;

Modifica una expresión de columna generada

No se permite modificar la expresión de una columna generada. En su lugar, debes descartar la columna existente y crear una columna generada nueva con la expresión nueva.

Visualiza las propiedades de una columna generada

INFORMATION_SCHEMA de Spanner contiene información sobre las columnas generadas en tu base de datos. Estos son algunos ejemplos de las preguntas que puedes responder si consultas el esquema de información.

¿Qué columnas generadas se definen en mi base de datos?

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

¿Cuál es el estado actual de las columnas generadas en la tabla Users?

Si agregaste una columna generada a una tabla existente, es posible que desees ver su estado actual para determinar, por ejemplo, si la columna se reabasteció y es totalmente utilizable. Si SPANNER_STATE muestra WRITE_ONLY en la siguiente consulta, significa que Spanner aún está en proceso de reabastecimiento de la columna y no se permiten lecturas.

SQL estándar de 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;

¿Qué sigue?