Lorsqu'une table est créée et qu'une erreur a été commise ou que les besoins de l'application changent, il est alors possible de la supprimer et de la récréer. Cela n'est toutefois pas pratique si la table contient déjà des données ou qu'elle est référencée par d'autres objets de la base de données (une contrainte de clé étrangère, par exemple). C'est pourquoi PostgreSQL offre une série de commandes permettant de modifier une table existante. Cela n'a rien à voir avec la modification des données contenues dans la table ; il ne s'agit ici que de modifier la définition, ou structure, de la table.
Il est possible :
d'ajouter des colonnes ;
de supprimer des colonnes ;
d'ajouter des contraintes ;
de supprimer des contraintes ;
de modifier des valeurs par défaut ;
de modifier les types de données des colonnes ;
de renommer des colonnes ;
de renommer des tables.
Toutes ces actions sont réalisées à l'aide de la commande ALTER TABLE, dont la page de référence est bien plus détaillée.
La commande d'ajout d'une colonne ressemble à :
ALTER TABLE produits ADD COLUMN description text;
La nouvelle colonne est initialement remplie avec la valeur par défaut
précisée (NULL en l'absence de clause DEFAULT).
À partir de PostgreSQL 11, ajouter une colonne
avec une valeur par défaut constante ne signifie plus que chaque ligne
de la table doit être mise à jour quand l'instruction ALTER
TABLE doit être exécutée. À la place, la valeur par défaut
sera renvoyée à chaque accès à la ligne et appliquée quand la table est
réécrite, rendant ainsi la commande ALTER TABLE bien
plus rapide, même sur des tables volumineuses.
Néanmoins, si la valeur par défaut est volatile (par exemple
clock_timestamp()), chaque ligne devra être mise à
jour avec la valeur calculée à l'exécution du ALTER
TABLE. Pour éviter une opération de mise à jour
potentiellement longue, et en particulier si vous avez de toute façon
l'intention de remplir la colonne avec des valeurs qui ne sont pas par
défaut, il pourrait être préférable d'ajouter la colonne sans valeur par
défaut, d'insérer les valeurs correctes en utilisant l'instruction
UPDATE, et enfin d'ajouter la valeur par désirée
comme décrit ci-dessous.
Des contraintes de colonne peuvent être définies dans la même commande, à l'aide de la syntaxe habituelle :
ALTER TABLE produits ADD COLUMN description text CHECK (description <> '');
En fait, toutes les options applicables à la description d'une colonne
dans CREATE TABLE peuvent être utilisées ici. Il ne
faut toutefois pas oublier que la valeur par défaut doit satisfaire les
contraintes données. Dans le cas contraire, ADD
échoue. Il est aussi possible d'ajouter les contraintes ultérieurement
(voir ci-dessous) après avoir rempli la nouvelle colonne correctement.
La commande de suppression d'une colonne ressemble à celle-ci :
ALTER TABLE produits DROP COLUMN description;
Toute donnée dans cette colonne disparaît. Les contraintes de table
impliquant la colonne sont également supprimées. Néanmoins, si la colonne
est référencée par une contrainte de clé étrangère d'une autre table,
PostgreSQL ne supprime pas silencieusement
cette contrainte. La suppression de tout ce qui dépend de la colonne peut
être autorisée en ajoutant CASCADE :
ALTER TABLE produits DROP COLUMN description CASCADE;
Voir la Section 5.15 pour une description du mécanisme général.
PostgreSQL ne supporte pas les contraintes
CHECK qui référencent les données d'autres tables que
celle contenant la nouvelle ligne ou la ligne mise à jour en cours de
vérification. Alors qu'une contrainte CHECK qui viole
cette règle pourrait apparaitre fonctionner dans des tests simples, il
est possible que la base de données atteigne un état dans lequel la
condition de la contrainte est fausse (à cause de changements
supplémentaires en dehors de la ligne impliquée). Ceci sera la cause
d'un échec du rechargement de la sauvegarde d'une base. La restauration
pourrait échouer même quand l'état complet de la base est cohérent avec
la contrainte, à cause de lignes chargées dans un autre différent qui
satisferait la contrainte. Si possible, utilisez les contraintes
UNIQUE, EXCLUDE, et
FOREIGN KEY pour exprimer des restrictions
inter-lignes et inter-tables.
Si ce que vous désirez est une vérification unique avec certaines lignes au moment de l'insertion, plutôt qu'une garantie de cohérence maintenue en permanence, un trigger personnalisé peut être utilisé pour l'implémenter. (Cette approche évite le problème de sauvegarde/restauration car pg_dump ne réinstalle les triggers qu'après chargement des données, donc cette vérification ne sera pas effectuée pendant une sauvegarde/restauration.)
PostgreSQL suppose que les conditions des
contraintes CHECK sont immutables, c'est-à-dire
qu'elles donneront toujours le même résultat pour la même ligne en
entrée. Cette supposition est ce qui justifie l'examen des contraintes
CHECK uniquement quand les lignes sont insérées ou
mises à jour, et non pas à d'autres moments. (Cet avertissement sur la
non référence aux données d'autres tables est en fait un cas particulier
de cette restriction.)
Un exemple d'une façon habituelle de casser cette supposition est de
référencer une fonction utilisateur dans une expression
CHECK, puis de changer le comportement de cette
fonction. PostgreSQL n'interdit pas cela,
mais il ne notera pas qu'il y a des lignes dans la table qui violent
maintenant la contrainte CHECK. Ceci sera la cause
d'un échec de la restauration d'une sauvegarde de cette base. La façon
recommandée de gérer de tels changements revient à supprimer la
contrainte (en utilisant ALTER TABLE), d'ajuster la
définition de la fonction, et d'ajouter de nouveau la contrainte, ce qui
causera une nouvelle vérification des lignes de la table.
Pour ajouter une contrainte, la syntaxe de contrainte de table est utilisée. Par exemple :
ALTER TABLE produits ADD CHECK (nom <> ''); ALTER TABLE produits ADD CONSTRAINT autre_nom UNIQUE (no_produit); ALTER TABLE produits ADD FOREIGN KEY (id_groupe_produit) REFERENCES groupes_produits;
Pour ajouter une contrainte NOT NULL, qui ne peut pas être écrite sous forme d'une contrainte de table, la syntaxe suivante est utilisée :
ALTER TABLE produits ALTER COLUMN no_produit SET NOT NULL;
La contrainte étant immédiatement vérifiée, les données de la table doivent satisfaire la contrainte avant qu'elle ne soit ajoutée.
Pour supprimer une contrainte, il faut connaître son nom. Si elle a été
explicitement nommée, il n'y a aucune difficulté. Dans le cas contraire,
le système a affecté un nom généré qu'il faudra identifier. La commande
\d de
psql peut être utile ici ; d'autres
interfaces offrent aussi la possibilité d'examiner les détails de table.
La commande est :
table
ALTER TABLE produits DROP CONSTRAINT un_nom;
(Dans le cas d'un nom de contrainte généré par le système, comme
$2, il est nécessaire de l'entourer de guillemets
doubles (") pour en faire un identifiant
valable.)
Comme pour la suppression d'une colonne, CASCADE peut
être ajouté pour supprimer une contrainte dont dépendent d'autres objets.
Une contrainte de clé étrangère, par exemple, dépend d'une contrainte de
clé primaire ou d'unicité sur la(les) colonne(s) référencée(s).
Cela fonctionne de la même manière pour tous les types de contraintes, à l'exception des contraintes NOT NULL. Pour supprimer une contrainte NOT NULL, on écrit :
ALTER TABLE produits ALTER COLUMN no_produit DROP NOT NULL;
(Les contraintes NOT NULL n'ont pas de noms.)
La commande de définition d'une nouvelle valeur par défaut de colonne ressemble à celle-ci :
ALTER TABLE produits ALTER COLUMN prix SET DEFAULT 7.77;
Cela n'affecte pas les lignes existantes de la table, mais uniquement la
valeur par défaut pour les futures commandes INSERT.
Pour retirer toute valeur par défaut, on écrit :
ALTER TABLE produits ALTER COLUMN prix DROP DEFAULT;
C'est équivalent à mettre la valeur par défaut à NULL. En conséquence, il n'y a pas d'erreur à retirer une valeur par défaut qui n'a pas été définie car NULL est la valeur par défaut implicite.
La commande de conversion du type de données d'une colonne ressemble à celle-ci :
ALTER TABLE produits ALTER COLUMN prix TYPE numeric(10,2);
Elle ne peut réussir que si chaque valeur de la colonne peut être
convertie dans le nouveau type par une conversion implicite. Si une
conversion plus complexe est nécessaire, une clause
USING peut être ajoutée qui indique comment calculer
les nouvelles valeurs à partir des anciennes.
PostgreSQL tente de convertir la valeur par défaut de la colonne le cas échéant, ainsi que toute contrainte impliquant la colonne. Mais ces conversions peuvent échouer ou produire des résultats surprenants. Il est souvent préférable de supprimer les contraintes de la colonne avant d'en modifier le type, puis d'ajouter ensuite les contraintes convenablement modifiées.
Pour renommer une colonne :
ALTER TABLE produits RENAME COLUMN no_produit TO numero_produit;