Créer et gérer des colonnes générées

Restez organisé à l'aide des collections Enregistrez et classez les contenus selon vos préférences.

Une colonne générée est une colonne qui est toujours calculée à partir d'autres colonnes d'une ligne. Ces colonnes peuvent simplifier une requête, économiser le coût d'évaluation d'une expression au moment de la requête, et être indexées ou utilisées en tant que clé étrangère. Cet article explique comment gérer ce type de colonne dans votre base de données.

Ajouter une colonne générée à une nouvelle table

Dans l'extrait de code CREATE TABLE suivant, nous créons une table pour stocker des informations sur les utilisateurs. Nous disposons de colonnes pour FirstName et LastName, et nous définissons une colonne générée pour FullName, qui est la concaténation de FirstName et 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)
);

La valeur de FullName est calculée lorsqu'une nouvelle ligne est insérée ou lorsque FirstName et/ou LastName sont mis à jour pour une ligne existante. La valeur calculée est stockée avec les autres colonnes de la table. Le code SQL entre parenthèses est appelé expression de génération.

  • expression peut être toute expression SQL valide pouvant être attribuée au type de données de la colonne avec les restrictions suivantes.

  • L'attribut STORED qui suit l'expression entraîne le stockage de la fonction avec les autres colonnes de la table. Les mises à jour ultérieures de l'une des colonnes référencées entraînent la ré-évaluation et le stockage de l'expression.

  • Les colonnes générées sans l'attribut STORED ne sont pas autorisées.

  • Les écritures directes sur les colonnes générées ne sont pas autorisées.

  • Vous ne pouvez pas utiliser les colonnes générées en tant que clé primaire ou en tant que partie d'une clé primaire. Elles peuvent toutefois être des clés d'index secondaires.

  • L'option allow_commit_timestamp n'est pas autorisée sur les colonnes générées ni sur les colonnes référencées par des colonnes générées.

  • Vous ne pouvez pas modifier le type de données d'une colonne générée, ni les colonnes référencées par la colonne générée.

  • Vous ne pouvez pas supprimer une colonne référencée par une colonne générée.

La colonne générée peut être interrogée comme n'importe quelle autre colonne, comme illustré dans l'exemple suivant.

GoogleSQL 

SELECT Id, FullName
FROM Users;

PostgreSQL 

SELECT id, fullname
FROM users;

Cela équivaut à l'instruction suivante, qui n'utilise pas la colonne générée.

GoogleSQL 

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

PostgreSQL 

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

Outre la simplification de la requête et l'économie du coût d'évaluation de l'expression au moment de la requête, une colonne générée peut également être indexée ou utilisée en tant que clé étrangère.

Créer un index sur une colonne générée

Pour faciliter les recherches dans la colonne générée FullName, nous pouvons créer un index secondaire, comme indiqué dans l'extrait suivant.

GoogleSQL 

CREATE INDEX UsersByFullName ON Users (FullName);

PostgreSQL 

CREATE INDEX UserByFullName ON users (fullname);

Ajouter une colonne générée à une table existante

À l'aide de l'instruction ALTER TABLE ci-dessous, nous ajoutons une colonne générée à la table Users pour générer et stocker les initiales de l'utilisateur.

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;

L'ajout d'une colonne générée à une table existante est une opération de longue durée qui permet de remplir les valeurs de la colonne. Lors du remplissage, les colonnes générées stockées ne peuvent pas être lues ni interrogées. L'état de remplissage est reflété dans INFORMATION_SCHEMA.

Créer un index partiel à l'aide d'une colonne générée

Imaginons que nous souhaitions n'interroger que les utilisateurs de plus de 18 ans. Une analyse complète de la table serait inefficace, c'est pourquoi nous utilisons un index partiel.

  1. Utilisez l'instruction suivante pour ajouter une autre colonne générée, qui renvoie l'âge de l'utilisateur s'il a plus de 18 ans et renvoie NULL dans le cas contraire.

    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;

  2. Créez un index sur cette nouvelle colonne et désactivez l'indexation des valeurs NULL avec le mot clé NULL_FILTERED dans Google SQL ou le prédicat IS NOT NULL dans PostgreSQL. Cet index partiel est plus petit et plus efficace qu'un index normal, car il exclut tous les utilisateurs âgés de 18 ans ou moins.

    GoogleSQL 

    CREATE NULL_FILTERED INDEX UsersAbove18ByAge
ON Users (AgeAbove18);

    PostgreSQL 

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

  3. Pour récupérer les propriétés Id et Age de tous les utilisateurs âgés de plus de 18 ans, exécutez la requête suivante.

    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;

  4. Pour filtrer sur un autre âge, par exemple, pour extraire tous les utilisateurs de plus de 21 ans, utilisez le même index et le même filtre sur la colonne générée comme suit :

    GoogleSQL 

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

    PostgreSQL 

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

Supprimer une colonne générée

L'instruction LDD suivante supprime une colonne générée à partir de la table Users.

ALTER TABLE Users
DROP COLUMN Initials;

Modifier une expression de colonne générée

Vous ne pouvez pas modifier l'expression d'une colonne générée. Vous devez donc supprimer la colonne existante et créer une colonne générée avec la nouvelle expression.

Afficher les propriétés d'une colonne générée

INFORMATION_SCHEMA de Spanner contient des informations sur les colonnes générées de votre base de données. Voici quelques exemples de questions auxquelles vous pouvez répondre en interrogeant le schéma d'information.

Quelles colonnes générées sont définies dans ma base de données ?

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

Quelle est l'état actuel des colonnes générées dans le tableau Users ?

Si vous avez ajouté une colonne générée à une table existante, vous pouvez afficher son état actuel pour déterminer, par exemple, si la colonne a été remplie et est entièrement utilisable. Si SPANNER_STATE renvoie WRITE_ONLY dans la requête suivante, cela signifie que Spanner est toujours en train de remplir la colonne et qu'aucune lecture n'est autorisée.

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;

Étapes suivantes