Se usó la API de Cloud Translation para traducir esta página.

Crear y administrar columnas generadas

Una columna generada es una columna que siempre se calcula a partir de otras columnas en una fila. Estas columnas pueden hacer que una consulta sea más simple, ahorrar el costo de evaluar una expresión en el momento de la consulta y se pueden indexar o usar 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 define una columna generada para FullName, que es la concatenación de FirstName y LastName.

GoogleSQL

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 columna con las siguientes restricciones.
- La expresión solo puede hacer referencia a columnas en la misma tabla.
- La expresión no puede contener subconsultas.
- La expresión no puede contener funciones no deterministas, como PENDING_COMMIT_TIMESTAMP(), CURRENT_DATE() y CURRENT_TIMESTAMP().
- No puedes modificar la expresión de una columna generada.
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.

GoogleSQL

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.

GoogleSQL

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 generada FullName, podemos crear un índice secundario como se muestra en el siguiente fragmento.

GoogleSQL

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.

GoogleSQL

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 generada 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 que se almacenan 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.

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

GoogleSQL

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;

Crea un índice en esta columna nueva y, luego, inhabilita la indexación de los valores NULL con la palabra clave NULL_FILTERED en Google SQL 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 todos los que son mayores de 18 años.
GoogleSQL
```
CREATE NULL_FILTERED INDEX UsersAbove18ByAge
ON Users (AgeAbove18);
```
PostgreSQL
```
CREATE INDEX UsersAbove18ByAge ON users (AgeAbove18)
WHERE AgeAbove18 IS NOT NULL;
```

Para recuperar el Id y el Age de todos los usuarios mayores de 18 años, ejecuta la siguiente consulta.

GoogleSQL

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;

Si deseas filtrar por edad, por ejemplo, para recuperar todos los usuarios que tienen más de 21 años, usa el mismo índice y filtra en la columna generada de la siguiente manera:
GoogleSQL
```
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 quita 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 nueva con la expresión nueva.

Visualiza las propiedades de una columna generada

Spanner 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 puede usarse por completo. Si SPANNER_STATE muestra WRITE_ONLY en la siguiente consulta, significa que Spanner aún está en proceso de reabastecimiento de la columna y que no se permiten lecturas.

GoogleSQL

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?

Obtén más información sobre el ESQUEMA DE INFORMACIÓN de Spanner.
Puedes ver más detalles sobre las columnas generadas en CREATE TABLE.