**Rappel sur les colonnes générées stockées** : Les colonnes générées _stockées_ existent depuis PostgreSQL 13. Dans cet exemple, on rajoute une colonne `volume` calculée à partir de trois autres. ```text DROP TABLE IF EXISTS paquets ; CREATE TABLE paquets ( id int PRIMARY KEY, longueur int, hauteur int, largeur int, t timestamptz, filler char(50) DEFAULT ' ' ) ; INSERT INTO paquets SELECT i, 1+mod(i,89), 1+mod(i,97), 1+mod(i,83), '2006-01-01'::timestamptz+i*interval '10m' FROM generate_series (1, 1_000_000) i ; ALTER TABLE paquets ADD COLUMN volume int GENERATED ALWAYS AS ((longueur * hauteur * largeur)) STORED; Temps : 420,294 ms \d paquets Table « public.paquets » Colonne | Type | C. | NULL-able | Par défaut ----------+-----------------+----+-----------+-------------------------------- id | integer | | not null | longueur | integer | | | hauteur | integer | | | largeur | integer | | | filler | character(50) | | | ' '::bpchar volume | integer | | | generated always as (longueur * hauteur * largeur) stored Index : "paquets_pkey" PRIMARY KEY, btree (id) ``` La colonne générée stockée `volume` est en lecture seule. Elle est calculée automatiquement à l'insertion, et recalculée automatiquement si les champs dont elle dépend sont mis à jour. Elle se comporte comme une colonne normale, peut porter une contrainte, et possède ses statistiques propres dans `pg_stats`. On peut l'indexer, ce qui peut être très pratique. Une sauvegarde logique avec `pg_dump` ne contient pas le contenu de la colonne générée, ce sera recalculé à la restauration. De même, la colonne générée stockée n'est exportée par la réplication logique que depuis PostgreSQL 18, et sur demande, pour les cas où la cible ne serait pas PostgreSQL. Noter que l'ajout d'une colonne générée stockée nécessite une réécriture complète de la table, ce qui est très lourd. Il n'est possible de modifier l'expression sans la supprimer d'abord que depuis PostgreSQL 17, et cela exige de réécrire la table. Il est possible d'utiliser des fonctions définies par l'utilisateur, mais en cas de modification de la fonction, il faut penser à réécrire la table (ce n'est pas automatique). **Principes des colonnes générées virtuelles** : PostgreSQL 18 apporte les colonnes générées _virtuelles_, qui se définissent de la même manière, mais avec le mot clé `VIRTUAL` à la fin : ```text ALTER TABLE paquets DROP COLUMN volume; ALTER TABLE paquets ADD COLUMN volume int GENERATED ALWAYS AS (longueur*hauteur*largeur) VIRTUAL ; \d paquets Table « public.paquets » Colonne | Type | C | NULL-able | Par défaut ----------+-----------------+---+-----------+--------------------------- id | integer | | not null | longueur | integer | | | hauteur | integer | | | largeur | integer | | | t | timestamp with… | | | filler | character(50) | | | ' '::bpchar volume | integer | | | generated always as (longueur * hauteur * largeur) ``` `VIRTUAL` n'apparaît pas dans la description et peut être omis, car c'est à présent le défaut pour une colonne générée. Par clarté, il vaut mieux écrire `VIRTUAL` en toute lettre à la fin. Il n'y a pas de risque pour les scripts existants, car le mot-clé `STORED` était obligatoire. **Évolution des colonnes virtuelles** : La table n'a pas besoin d'être réécrite pour ajouter une colonne virtuelle, le verrou est donc bien plus court. Comme pour une colonne générée stockée, on peut poser une contrainte. Sa création impose la lecture de la table pour vérification, ce peut être long. Modifier une colonne source violant cette contrainte bloquera une insertion : ```text ALTER TABLE paquets ADD CONSTRAINT vol_ck CHECK (volume>0); INSERT INTO paquets (id, largeur, longueur, hauteur) VALUES (2_000_000, 0, 1, 1) ; ERROR: new row for relation "paquets" violates check constraint "vol_ck" DÉTAIL : Failing row contains (100100, 1, 1, 0,..., virtual). ``` Si la fonction à calculer change, la modification est facile et ne pose pas de souci de cohérence. ```text ALTER TABLE paquets DROP CONSTRAINT vol_ck ; ALTER TABLE paquets ALTER COLUMN volume SET EXPRESSION AS (10*ceil (longueur*hauteur*largeur/10+1)::int) ; ``` La suppression préalable de la contrainte existante est nécessaire. En effet, PostgreSQL ne vérifie pas de lui-même qu'une contrainte existante restera valide, et il refuse la modification avec un message clair : ```text ERROR: ALTER TABLE / SET EXPRESSION is not supported for virtual generated columns in tables with check constraint ``` La contrainte devra être redéfinie ensuite, si pertinente. Un changement de type est trivial, mais peut exiger une relecture de la table : ```text ALTER TABLE paquets ALTER COLUMN volume TYPE bigint ; ALTER TABLE paquets ALTER COLUMN volume TYPE int ; ``` Supprimer l'expression (`ALTER TABLE … ALTER COLUMN … DROP EXPRESSION ;`) n'a pas de sens pour une colonne générée virtuelle, il faut supprimer la colonne. (À l'inverse, pour une colonne générée stockée, `DROP EXPRESSION` permet de conserver les valeurs déjà écrites.)