- Club d'entraide des développeurs francophones

Documentation PostgreSQL 8.2.5 -

Commandes SQL

Précédent
ALTER SEQUENCE
Suivant
ALTER TABLESPACE
Niveau supérieur.
Sommaire

ALTER TABLE

ALTER TABLE — Modifier la définition d'une table

Synopsis

ALTER TABLE [ ONLY ] nom [ * ]
    action [, ... ]
ALTER TABLE [ ONLY ] nom [ * ]
    RENAME [ COLUMN ] colonne TO nouvelle_colonne
ALTER TABLE nom
    RENAME TO nouveau_nom
ALTER TABLE nom
    SET SCHEMA nouveau_schema

où action peut être :

    ADD [ COLUMN ] colonne type [ contrainte_colonne [ ... ] ]
    DROP [ COLUMN ] colonne [ RESTRICT | CASCADE ]
    ALTER [ COLUMN ] colonne TYPE type [ USING expression ]
    ALTER [ COLUMN ] colonne SET DEFAULT expression
    ALTER [ COLUMN ] colonne DROP DEFAULT
    ALTER [ COLUMN ] colonne { SET | DROP } NOT NULL
    ALTER [ COLUMN ] colonne SET STATISTICS entier
    ALTER [ COLUMN ] colonne SET STORAGE { PLAIN | EXTERNAL | EXTENDED | MAIN }
    ADD contrainte_table
    DROP CONSTRAINT nom_contrainte [ RESTRICT | CASCADE ]
    DISABLE TRIGGER [ nom_declencheur | ALL | USER ]
    ENABLE TRIGGER [ nom_declencheur | ALL | USER ]
    CLUSTER ON nom_index
    SET WITHOUT CLUSTER
    SET WITHOUT OIDS
    SET ( paramètre_stockage = valeur [, ... ] )
    RESET ( paramètre_stockage [, ... ] )
    INHERIT table_parent
    NO INHERIT table_parent
    OWNER TO nouveau_proprietaire
    SET TABLESPACE nouvel_espacelogique

Description

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

ADD COLUMN

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

Il faut être propriétaire de la table pour utiliser ALTER TABLE . Pour modifier le schéma d'une table, le droit CREATE sur le nouveau schéma est requis. Pour ajouter la table en tant que nouvel enfant d'une table parent, vous devez aussi être propriétaire de la table parent. Pour modifier le propriétaire, il est nécessaire d'être un membre direct ou indirect du nouveau rôle et ce dernier doit avoir le droit CREATE sur le schéma de la table. (Ces restrictions assurent que la modification du propriétaire ne diffère en rien de ce qu'il est possible de faire par la suppression et le re-création de la table. Néanmoins, un superutilisateur peut modifier le propriétaire de n'importe quelle table.)

Paramètres

nom

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. Cela peut prendre un temps considérable pour une grande table ; et cela demande 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 de la possibilité de spécifier des changements multiples à l'aide d'une seule commande ALTER TABLE est la combinaison en une seule passe sur la table de plusieurs parcours et réécritures.

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

Le fait qu'ALTER TYPE requiert la réécriture de toute la table est parfois 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 une colonne conservée de la table et touttype est le type courant de la colonne. Sémantiquement, aucune modification n'est visible, mais la commande force la réécriture, ce qui supprime toute donnée devenue inutile.

L'option USING d'ALTER TYPE peut en fait utiliser une expression qui implique d'anciennes valeurs de la ligne ; c'est-à-dire qu'il peut être fait référence aussi bien aux autres colonnes qu'à celle en cours de conversion. Cela permet d'effectuer des conversions très générales à l'aide de la syntaxe ALTER TYPE. À cause de cette flexibilité, l'expression USING n'est pas appliquée à la valeur par défaut de la colonne (s'il y en a une) : le résultat pourrait ne pas être une expression constante requise pour une valeur par défaut. Lorsqu'il n'existe pas de transtypage, implicite ou d'affectation, entre les deux types, ALTER TYPE peut échouer à convertir la valeur par défaut alors même que la clause USING est spécifiée. Dans de ce cas, il convient de supprimer valeur par défaut avec DROP DEFAULT, d'exécuter ALTER TYPE et enfin d'utiliser SET DEFAULT pour ajouter une valeur par défaut appropriée. Des considérations similaires s'appliquent aux index et contraintes qui impliquent la colonne.

Si une table est héritée, 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. De ce fait, la commande ALTER TABLE ONLY est rejetée. Cela assure que les colonnes des tables descendantes correspondent toujours à celles de la table parent.

Un appel récursif à DROP COLUMN supprime la colonne d'une table descendante si et seulement si cette table n'hérite pas cette colonne d'une autre table et que la colonne n'y a pas été définie indépendamment de tout héritage. Une suppression non récursive de colonne ( ALTER TABLE ONLY ... DROP COLUMN ) ne supprime jamais les colonnes descendantes ; elles sont marquées comme définies de manière indépendante, plutôt qu'héritées.

Les actions TRIGGER, CLUSTER, OWNER, et TABLESPACE ne sont jamais propagées aux tables descendantes ; c'est-à-dire qu'elles agissent comme si ONLY est spécifié. Seules les ajouts de contraintes CHECK peuvent être propagés.

Tout changement sur une table du catalogue système est interdit.

Voir la commande CREATE TABLE pour avoir une description plus complète des paramètres valides. Chapitre 5, Définition des données fournit de plus amples informations sur l'héritage.

Exemples

Ajouter une colonne de type varchar à une table :

ALTER TABLE distributeurs ADD COLUMN adresse varchar(30);

Supprimer une colonne de table :

ALTER TABLE distributeurs DROP COLUMN adresse RESTRICT;

Changer les types de deux colonnes en une seule opération :

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

Convertir une colonne de type integer (entier) contenant une estampille temporelle UNIX en timestamp with time zone à l'aide d'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';

La même, quand la colonne a une expression par défaut qui ne sera pas convertie automatiquement vers le nouveau type de données :

ALTER TABLE foo
    ALTER COLUMN foo_timestamp DROP DEFAULT,
    ALTER COLUMN foo_timestamp TYPE timestamp with time zone
    USING
        timestamp with time zone 'epoch' + foo_timestamp * interval '1 second',
    ALTER COLUMN foo_timestamp SET DEFAULT now();

Renommer une colonne existante :

ALTER TABLE distributeurs RENAME COLUMN adresse TO city;

Renommer une table existante :

ALTER TABLE distributeurs RENAME TO fournisseurs;

Ajouter une contrainte NOT NULL à une colonne :

ALTER TABLE distributeurs ALTER COLUMN rue SET NOT NULL;

Supprimer la contrainte NOT NULL d'une colonne :

ALTER TABLE distributeurs ALTER COLUMN rue DROP NOT NULL;

Ajouter une contrainte de vérification sur une table :

ALTER TABLE distributeurs ADD CONSTRAINT verif_cp CHECK (char_length(code_postal) = 5);

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

ALTER TABLE distributeurs DROP CONSTRAINT verif_cp;

Ajouter une contrainte de clé étrangère à une table :

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

Ajouter une contrainte unique (multicolonnes) à une table :

ALTER TABLE distributeurs ADD CONSTRAINT dist_id_codepostal_key UNIQUE (dist_id, code_postal);

Ajouter une clé primaire nommée automatiquement à une table. Une table ne peut jamais avoir qu'une seule clé primaire.

ALTER TABLE distributeurs ADD PRIMARY KEY (dist_id);

Déplacer une table dans un tablespace différent :

ALTER TABLE distributeurs SET TABLESPACE tablespacerapide;

Déplacer une table dans un schéma différent :

ALTER TABLE mon_schema.distributeurs SET SCHEMA votre_schema;

Compatibilité

Les formes ADD, DROP et SET DEFAULT se conforment au standard SQL. Les autres formes sont des extensions PostgreSQL™, tout comme la possibilité de spécifier plusieurs manipulations en une seule commande ALTER TABLE .

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

ALTER SEQUENCE

ALTER TABLESPACE Suivant