ALTER TABLE

Description

ALTER TABLE change la définition d'une table existante. Il existe plusieurs variantes :

Toutes les actions à l'exception de RENAME peuvent être combinées dans une liste de plusieurs altérations à appliquer en parallèle. Par exemple, il est possible d'ajouter plusieurs colonnes et/ou modifier le type de plusieurs colonnes en une seule commande. Ceci est particulièrement utile avec les grosses tables car seule une passe sur la table sera nécessaire.

Il faut être propriétaire de la table pour utiliser ALTER TABLE, sauf pour ALTER TABLE OWNER, qui ne peut être utilisée que par un super utilisateur.

Paramètres

Notes

Le mot clé COLUMN n'est pas nécessaire. Il peut être omis.

Quand une colonne est ajoutée avec ADD COLUMN, toutes les lignes existantes de cette table sont initialisées avec la valeur par défaut de la colonne (NULL si aucune clause DEFAULT n'a été définie).

Ajouter une colonne avec une valeur par défaut différente de NULL ou modifier le type d'une colonne existante requiert que la table entière soit ré-écrite. Ceci pourrait prendre un temps considérable pour une grande table ; et cela demandera temporairement le double d'espace disque.

Ajouter une contrainte CHECK ou NOT NULL requiert de parcourir la table pour vérifier que les lignes existantes respectent cette contrainte.

La raison principale pour fournir l'option de spécification de changements multiples en une seule commande ALTER TABLE est que les parcours ou ré-écritures multiples de table peuvent être combinés en une seule passe sur la table.

La forme DROP COLUMN ne supprime pas physiquement la colonne, mais la rend simplement invisible au SQL. Par la suite, les ordres d'insertion et de mise à jour sur cette table stockeront une valeur NULL pour la colonne. Du coup, supprimer une colonne ne réduit pas immédiatement la taille de la table sur le disque car l'espace occupé par la colonne n'est pas récupéré. Cet espace sera récupéré petit à petit, au fur et à mesure des mises à jour des lignes de la table.

Le fait qu'ALTER TYPE requiert la ré-écriture de la table entière est quelque fois un avantage car le processus de ré-écriture élimine tout espace mort dans la table. Par exemple, pour réclamer immédiatement la place occupée par une colonne supprimée, la façon la plus rapide est

ALTER TABLE table ALTER COLUMN toutecolonne TYPE touttype;

où toutecolonne est toute colonne de table restante et touttype est le même type que ce que possède déjà la colonne. Cela résulte en des modifications sémantiquement visibles dans la table mais la commande force la ré-écriture, qui oublie toute donnée inutile.

L'option USING d'ALTER TYPE peut en fait spécifier toute expression impliquant les anciennes valeurs de la ligne ; c'est-à-dire qu'il peut référencer les autres colonnes ainsi que celle en cours de conversion. Ceci permet des conversions très générales grâce à la syntaxe ALTER TYPE. À cause de cette flexibilité, l'expression USING n'est pas appliquée à la valeur par défaut de la colonne (si elle en a) ; le résultat pourrait ne pas être une expression constante requise pour une valeur par défaut. Cela signifie que, quand il n'existe pas de conversion implicite ou d'affectation de l'ancien au nouveau type, ALTER TYPE pourrait échouer dans sa conversion de la valeur par défaut bien que la clause USING soit spécifiée. Dans de tels cas, supprimez la valeur par défaut avec DROP DEFAULT, exécutez le ALTER TYPE et enfin utilisez SET DEFAULT pour ajouter une nouvelle valeur par défaut convenable. Des considérations similaires s'appliquent aux index et contraintes impliquant la colonne.

Si une table a des tables descendantes, il n'est pas possible d'ajouter, de renommer ou de modifier le type d'une colonne dans la table parent sans le faire aussi pour ses descendantes. Donc, la commande ALTER TABLE ONLY est rejetée. Ceci assure que les descendantes ont toujours des colonnes correspondant à celles de la table parente.

Un appel récursif à DROP COLUMN supprimera une colonne d'une table descendante si et seulement si la table descendante n'hérite pas de cette colonne d'une autre table et n'a jamais eu de définition indépendante de la colonne. Une suppression de colonne non récursive (c'est à dire une commande ALTER TABLE ONLY ... DROP COLUMN) ne supprime jamais les colonnes descendantes mais les marque comme définies de manière indépendante, plutôt qu'héritées.

On ne peut pas changer quoi que ce soit dans une table du catalogue système.

Voir la commande CREATE TABLE pour avoir une description plus complète des paramètres valides. Chapitre 5 donne plus d'informations sur l'héritage.

Exemples

Pour ajouter une colonne de type varchar à une table :

ALTER TABLE distributeurs ADD COLUMN adresse varchar(30);

Pour supprimer une colonne d'une table :

ALTER TABLE distributeurs DROP COLUMN adresse RESTRICT;

Pour modifier les types de deux colonnes existantes en une opération :

ALTER TABLE distributeurs
    ALTER COLUMN adresse TYPE varchar(80),
    ALTER COLUMN nom TYPE varchar(100);

Pour modifier une colonne de type entier contenant une date/heure UNIX en timestamp with time zone avec une clause USING :

ALTER TABLE foo
    ALTER COLUMN foo_timestamp TYPE timestamp with time zone
    USING
        timestamp with time zone 'epoch' + foo_timestamp * interval '1 second';

Pour renommer une colonne existante :

ALTER TABLE distributeurs RENAME COLUMN adresse TO city;

Pour renommer une table existante :

ALTER TABLE distributeurs RENAME TO suppliers;

Pour ajouter une contrainte NOT NULL à une colonne :

ALTER TABLE distributeurs ALTER COLUMN rue SET NOT NULL;

Pour supprimer une contrainte NOT NULL d'une colonne :

ALTER TABLE distributeurs ALTER COLUMN rue DROP NOT NULL;

Pour ajouter une contrainte de vérification sur une table :

ALTER TABLE distributeurs ADD CONSTRAINT verif_zip CHECK (char_length(zipcode) = 5);

Pour supprimer une contrainte de vérification d'une table et de toutes ses tables filles :

ALTER TABLE distributeurs DROP CONSTRAINT verif_zip;

Pour ajouter une contrainte de clé étrangère à une table :

ALTER TABLE distributeurs ADD CONSTRAINT distfk FOREIGN KEY (adresse) REFERENCES adresses (adresse) MATCH FULL;

Pour ajouter une contrainte unique (multicolonnes) à une table :

ALTER TABLE distributeurs ADD CONSTRAINT dist_id_zipcode_key UNIQUE (dist_id, zipcode);

Pour ajouter une clé primaire nommée automatiquement à une table. Remarque : une table ne peut avoir qu'une seule clé primaire dans toute sa vie.

ALTER TABLE distributeurs ADD PRIMARY KEY (dist_id);

Pour déplacer une table dans un tablespace différent :

ALTER TABLE distributeurs SET TABLESPACE espacelogiquerapide;

Compatibilité

Les formes ADD, DROP et SET DEFAULT se conforment au standard SQL. Les autres formes sont des extensions PostgreSQL au standard SQL. De plus, la capacité à spécifier plus d'une manipulation en une seule commande ALTER TABLE est une extension.

ALTER TABLE DROP COLUMN peut être utilisé pour supprimer la seule colonne d'une table, laissant une table sans colonne. C'est une extension de SQL, qui ne permet pas les tables sans colonne.