Documentation PostgreSQL 14.22 » Langage SQL » Définition des données » Colonnes générées

Précédent | Niveau supérieur | Suivant
5.2. Valeurs par défaut | Sommaire | 5.4. Contraintes

## 5.3. Colonnes générées

Une colonne générée est une colonne spéciale, toujours calculée à partir d'autres colonnes. Elle est donc aux colonnes ce qu'une vue est aux tables. Il y a deux types de colonnes générées : stockée et virtuelle. Une colonne générée stockée est calculée quand elle est écrite (insérée ou mise à jour) et occupe de l'espace de stockage comme si elle était une colonne normale. Une colonne virtuelle générée n'occupe pas d'espace et est calculée à la lecture. Une colonne générée virtuelle est donc équivalente à une vue, et une colonne générée stockée à une vue matérialisée (sauf qu'elle sera toujours mise à jour automatiquement). PostgreSQL n'implémente actuellement que les colonnes générées stockées.

Pour créer une colonne générée, utilisez la clause `GENERATED ALWAYS AS` de la commande `CREATE TABLE`, par exemple :

    CREATE TABLE people (
        ...,
        height_cm numeric,
        height_in numeric GENERATED ALWAYS AS (height_cm / 2.54) STORED
    );

Le mot-clé `STORED` doit être spécifié pour choisir le type de colonne générée. Voir CREATE TABLE pour plus de détails.

On ne peut écrire directement dans une colonne générée. Une valeur ne peut pas y être affectée dans les commandes `INSERT` ou `UPDATE`, mais le mot-clé `DEFAULT` peut l'être.

Voyons les différences entre une colonne avec une valeur par défaut et une colonne générée. La colonne par défaut est calculée une seule fois à la première insertion de la ligne si aucune autre valeur n'est fournie ; une colonne générée est mise à jour à chaque fois que la ligne change et on ne peut y déroger. Une valeur par défaut d'une colonne ne peut se référer à d'autres colonnes de la table ; mais c'est ce que fait normalement une expression générée. Une valeur par défaut d'une colonne peut utiliser des fonctions volatiles, par exemple `random()` ou des fonctions se référant au temps actuel ; ce n'est pas permis pour les colonnes générées.

Il existe plusieurs restrictions dans la définition des colonnes générées et des tables qui les utilisent :

- L'expression pour générer les valeurs ne peut utiliser que des fonctions immutables, ne peut pas utiliser de sous-requêtes, ni référencer d'aucune manière quoi que ce soit hors de la ligne en cours.

- Une expression ne peut référencer une autre colonne générée.

- Une expression ne peut référencer une colonne système, sauf `tableoid`.

- Une colonne générée ne peut avoir une valeur par défaut ou être définie comme colonne identité.

- Une colonne générée ne peut faire partie d'une clé de partitionnement.

- Les tables distantes peuvent porter des colonnes générées. Voir CREATE FOREIGN TABLE pour les détails.

- Pour l'héritage :

  - Si une colonne parent est une colonne générée, une colonne enfant doit aussi être une colonne générée utilisant la même expression. Dans la définition de la colonne enfant, laissez la clause `GENERATED` car elle sera copiée du parent.

  - Dans le cas d'héritage multiple, si une colonne parent est une colonne générée, alors toutes les colonnes parents doivent être des colonnes générées et avec la même expression.

  - Si une colonne parent n'est pas une colonne générée, une colonne enfant pourrait être définie comme colonne générée ou pas.

D'autres considérations s'appliquent à l'utilisation des colonnes générées.

- Les colonnes générées maintiennent les droits d'accès séparément des colonnes sur lesquelles elles sont basées. On peut donc s'arranger pour qu'un rôle défini puisse lire une colonne générée mais pas la colonne de base sous-jacente.

- Conceptuellement, les colonnes générées sont mises à jour après le déclenchement des triggers `BEFORE`. Les changements dans les colonnes de base au sein d'un trigger `BEFORE` seront donc répercutés dans les colonnes générées. Mais à l'inverse, il n'est pas permis d'accéder aux colonnes générées dans les triggers `BEFORE`.