PostgreSQLLa base de données la plus sophistiquée au monde.
Documentation PostgreSQL 18 beta 2 » Langage SQL » Définition des données » Colonnes générées

5.4. 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 est équivalente à une vue matérialisée (sauf qu'elle sera toujours mise à jour automatiquement).

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)
);

Une colonne générée est par défaut de type virtuel. Utilisez les mots-clés VIRTUAL ou STORED pour rendre le choix explicite. 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 virtuelle générée ne peut pas avoir un type défini par l'utilisateur, et l'expression de génération d'une colonne virtuelle générée ne doit pas référencer des fonctions ou types définis par un utilisateur. Autrement dit, elle peut seulement utiliser des fonctions et types natifs. Ceci s'applique aussi indirectement, par exemple aux fonctions ou types utilisés par des opérateurs ou conversions. (Cette restriction n'existe pas pour les colonnes générées stockées.)

  • 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 et le partitionnement :

    • Si une colonne parent est une colonne générée, la colonne enfant doit aussi être une colonne générée de même type (stockée ou virtuelle). Néanmoins, la colonne enfant peut avoir une expression de génération différente.

      Pour les colonnes générées stockées, lors de l'insertion ou de la mise à jour d'une ligne, l'expression de génération réellement appliquée est celle associée à la table contenant physiquement la ligne. (Ceci est contraire au comportement pour la valeur par défaut d'une colonne : pour ces dernières, la valeur par défaut appliquée est celle associée avec la table nommée dans la requête.) Pour les colonnes générées virtuelles, l'expression de génération de la table nommée dans la requête s'applique lors de la lecture de la table.

    • Si une colonne parent n'est pas une colonne générée, sa colonne enfant ne doit pas non plus être une colonne générée.

    • Pour les tables héritées, si vous écrivez une définition de colonne de la table enfant sans clause GENERATED dans CREATE TABLE ... INHERITS, alors sa clause GENERATED sera automatiquement copiée du parent. ALTER TABLE ... INHERIT insistera que les colonnes parents et enfants correspondant déjà au statut de génération, mais il n'obligera pas à ce que les expressions de génération correspondent.

    • De façon similaire pour les tables partitionnées, si vous écrivez la définition d'une colonne enfant sans la clause GENERATED clause dans CREATE TABLE ... PARTITION OF, alors la clause GENERATED sera automatiquement copiée du parent. ALTER TABLE ... ATTACH PARTITION insistera pour que les colonnes parents et enfants correspondent déjà au niveau du statut de génération, mais il ne reclamera pas que les expressions de génération correspondent.

    • Dans le cas de plusieurs héritages, si une des colonnes parents est une colonne générée, alors toutes les colonnes parents doivent être des colonnes générées. Si elles n'ont pas toutes la même expression de génération, alors l'expression désirée pour l'enfant doit être indiquée explicitement.

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.

    Pour les colonnes générées virtuelles, ce n'est complètement sécurisé que si la génération utilise des fonctions leakproofs (voir CREATE FUNCTION), mais ce n'est pas imposé par le système.

  • Les privilèges des fonctions utilisées dans les expressions de génération sont contrôlées au moment où la fonction est exécutée, à l'écriture ou à la lecture, comme si l'expression avait été appelée directement depuis la requête utilisant la colonne générée. L'utilisateur d'une colonne générée doit avoir la permission d'appeler toutes les fonctions utilisées par l'expression de génération. Les fonctions au sein de celle-ci sont exécutées avec les privilèges de l'utilisateur exécutant la requête ou le propriétaire de la fonction, selon que la fonction est définie SECURITY INVOKER ou SECURITY DEFINER.

  • 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.

  • Les colonnes générées peuvent être répliquées lors d'une réplication logique grâce au paramètre publish_generated_columns de CREATE PUBLICATION, ou en les incluant dans la liste de colonnes de la commande CREATE PUBLICATION. Pour le moment, ce n'est supporté que pour les colonnes générées stockées. Voir Section 29.6 pour les détails.