Menu

PostgreSQL ALTER TABLE : ajouter, renommer, supprimer

ALTER TABLE modifie une table existante : ajouter, renommer et supprimer des colonnes, changer le type d'une colonne avec USING, définir des valeurs par défaut et NOT NULL, et ajouter des contraintes.

Cette page contient des éditeurs exécutables. Modifiez, exécutez et voyez la sortie instantanément.

ALTER TABLE modifie une table qui existe déjà. Les formes les plus utiles sont ADD COLUMN, RENAME COLUMN ... TO, DROP COLUMN et ALTER COLUMN ... TYPE. Plusieurs actions peuvent tenir dans une seule instruction, séparées par des virgules.

Les lignes existantes reçoivent 'free' dans plan et NULL dans signed_up. Depuis PostgreSQL 11, ajouter une colonne avec une valeur par défaut constante ne réécrit pas la table, donc c'est rapide même sur une grosse table. Une valeur par défaut volatile comme clock_timestamp() réécrit toujours chaque ligne.

Renommer une colonne ou une table

Un renommage est instantané et conserve les données, les index et les contraintes. Les vues continuent de fonctionner, car elles pointent vers l'identifiant interne de la table, pas vers son nom. Les noms écrits en texte ailleurs ne suivent pas : le corps des fonctions PL/pgSQL et les requêtes de l'application utilisent toujours l'ancien nom, alors cherchez-le avant de renommer.

Supprimer une colonne

DROP COLUMN supprime la colonne et ses données. L'instruction échoue si une vue ou une clé étrangère dépend de la colonne. CASCADE les supprime aussi, et IF EXISTS évite l'erreur quand la colonne n'existe déjà plus.

PostgreSQL ne réécrit pas la table ici. Il marque la colonne comme supprimée, et l'espace est récupéré au fil des mises à jour des lignes ou quand la table est réécrite.

Changer le type d'une colonne

ALTER COLUMN ... TYPE convertit chaque valeur. Quand il n'existe pas de conversion automatique de l'ancien type vers le nouveau, ajoutez USING avec une expression qui calcule la nouvelle valeur :

Sans USING, cette instruction échoue avec column "price" cannot be cast automatically to type numeric. Si une seule valeur ne peut pas être convertie, toute l'instruction échoue et la table reste inchangée.

La plupart des changements de type réécrivent la table. Certains non : allonger un varchar(n), ou passer de varchar à text.

Définir ou retirer une valeur par défaut et NOT NULL

SET NOT NULL parcourt la table et échoue si une ligne contient un NULL, alors comblez les trous d'abord :

DROP DEFAULT et DROP NOT NULL les annulent. Une nouvelle valeur par défaut ne s'applique qu'aux insertions futures. Les lignes existantes gardent leurs valeurs.

Ajouter une contrainte

ALTER TABLE orders ADD CONSTRAINT total_positive CHECK (total > 0);
ALTER TABLE orders DROP CONSTRAINT total_positive;

Ajouter un CHECK ou une clé étrangère parcourt toute la table pour la valider. Sur une grosse table très sollicitée, ajoutez-la d'abord avec NOT VALID, qui ne vérifie que les nouvelles lignes. Lancez ensuite ALTER TABLE orders VALIDATE CONSTRAINT total_positive;. Cette commande vérifie les anciennes lignes sous un verrou plus faible, qui laisse passer les lectures et les écritures. La page sur les clés étrangères utilise la même méthode, et la référence d'ALTER TABLE liste chaque forme et son verrou.

Questions fréquentes

Peut-on changer l'ordre des colonnes dans PostgreSQL ?

Non. ALTER TABLE ne peut pas déplacer une colonne, et une nouvelle colonne est toujours ajoutée à la fin. Listez les colonnes dans l'ordre voulu dans votre SELECT, ou créez une nouvelle table dans cet ordre et copiez-y les données.

ALTER TABLE verrouille-t-il la table dans PostgreSQL ?

La plupart des formes prennent un verrou ACCESS EXCLUSIVE, qui bloque lectures et écritures jusqu'à la fin de l'instruction. Les changements rapides (renommage, suppression de colonne, ajout d'une colonne avec une valeur par défaut constante) ne le gardent qu'un instant. Un changement de type qui réécrit la table le garde pendant toute la réécriture.

Comment ajouter plusieurs colonnes d'un coup dans PostgreSQL ?

Séparez les actions par des virgules dans une seule instruction : ALTER TABLE users ADD COLUMN phone text, ADD COLUMN city text;. Toutes les actions d'un même ALTER TABLE s'exécutent ensemble, sous un seul verrou.

Peut-on annuler un ALTER TABLE dans PostgreSQL ?

Oui. Le DDL de PostgreSQL est transactionnel, donc un ALTER TABLE placé dans BEGIN ... ROLLBACK est entièrement annulé.

Illustration des langages de programmation de Coddy

Apprendre à coder avec Coddy

Commencer