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 [ SET DATA ] 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 ]
ENABLE REPLICA TRIGGER nom_trigger
ENABLE ALWAYS TRIGGER nom_trigger
DISABLE RULE nom_regle_reecriture
ENABLE RULE nom_regle_reecriture
ENABLE REPLICA RULE nom_regle_reecriture
ENABLE ALWAYS RULE nom_regle_reecriture
CLUSTER ON nom_index
SET WITHOUT CLUSTER
SET WITH OIDS
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
ALTER TABLE modifie la définition d'une table existante. Il existe plusieurs variantes :
Ajoute une nouvelle colonne à la table en utilisant une syntaxe identique à celle de CREATE TABLE.
Supprime une colonne de la table. Les index et les contraintes de table référençant cette colonne sont automatiquement supprimés. L'option CASCADE doit être utilisée lorsque des objets en dehors de la table dépendent de cette colonne, comme par exemple des références de clés étrangères ou des vues.
Change le type d'une colonne de la table. Les index et les contraintes simples de table qui impliquent la colonne sont automatiquement convertis pour utiliser le nouveau type de la colonne en réanalysant l'expression d'origine. La clause optionnelle USING précise comment calculer la nouvelle valeur de la colonne à partir de l'ancienne ; en cas d'omission, la conversion par défaut est identique à une affectation de transtypage de l'ancien type vers le nouveau. Une clause USING doit être fournie s'il n'existe pas de conversion implicite ou d'assignement entre les deux types.
Ajoute ou supprime les valeurs par défaut d'une colonne. Les valeurs par défaut ne s'appliquent qu'aux commandes INSERT ultérieures. Elles ne modifient pas les lignes déjà présentes dans la table. Des valeurs par défaut peuvent aussi être créées pour les vues. Dans ce cas, elles sont ajoutées aux commandes INSERT de la vue avant que la règle ON INSERT de la vue ne soit appliquée.
Modifie l'autorisation de valeurs NULL. SET NOT NULL ne peut être utilisé que si la colonne ne contient pas de valeurs NULL.
Permet de modifier l'objectif de collecte de statistiques par colonne pour les opérations d'analyse (ANALYZE) ultérieures. L'objectif prend une valeur entre 0 et 10000. il est positionné à -1 pour utiliser l'objectif de statistiques par défaut du système (default_statistics_target). Pour plus d'informations sur l'utilisation des statistiques par le planificateur de requêtes de PostgreSQL™, voir Section 14.2, « Statistiques utilisées par le planificateur ».
Modifie le mode de stockage pour une colonne. Cela permet de contrôler si cette colonne est conservée en ligne ou dans une deuxième table, appelée table TOAST, et si les données sont ou non compressées. PLAIN, en ligne, non compressé, est utilisé pour les valeurs de longueur fixe, comme les integer. MAIN convient pour les données en ligne, compressibles. EXTERNAL est fait pour les données externes non compressées, EXTENDED pour les données externes compressées. EXTENDED est la valeur par défaut pour la plupart des types qui supportent les stockages différents de PLAIN. L'utilisation d'EXTERNAL permet d'accélérer les opérations d'extraction de sous-chaînes sur les très grosses valeurs de types text et bytea mais utilise plus d'espace de stockage. SET STORAGE ne modifie rien dans la table, il configure la stratégie à poursuivre lors des mises à jour de tables suivantes. Voir Section 53.2, « TOAST » pour plus d'informations.
Ajoute une nouvelle contrainte à une table en utilisant une syntaxe identique à CREATE TABLE.
Supprime la contrainte de table précisée.
Configure l'exécution des déclencheurs définis sur la table. Un déclencheur désactivé est toujours connu par le système mais n'est plus exécuté lorsque l'événement déclencheur survient. Pour un déclencheur retardé, le statut d'activité est vérifié au moment où survient l'événement, et non quand la fonction du déclencheur est réellement exécutée. Il est possible de désactiver ou d'activer un déclencheur spécifique (précisé par son nom), tous les déclencheurs d'une table ou seulement les déclencheurs utilisateur de cette table (cette option exclut les déclencheurs utilisés pour implanter les contraintes de clés étrangères). Désactiver ou activer les déclencheurs de contraintes requiert des droits de superutilisateur ; cela doit se faire avec précaution car l'intégrité de la contrainte ne peut pas être garantie si les déclencheurs ne sont pas exécutés. Le mécanisme de déclenchement des triggers est aussi affecté par la variable de configuration session_replication_role. Les triggers activés (ENABLE) se déclencheront quand le rôle de réplication est « origin » (la valeur par défaut) ou « local ». Les triggers configurés ENABLE REPLICA se déclencheront seulement si la session est en mode « replica » et les triggers ENABLE ALWAYS se déclencheront à chaque fois, quelque soit le mode de réplication.
Ces formes configurent le déclenchement des règles de ré-écriture appartenant à la table. Une règle désactivée est toujours connue par le système mais non appliquée lors de la ré-écriture de la requête. La sémantique est identique celles des triggers activés/désactivés. Cette configuration est ignorée pour les règles ON SELECT qui sont toujours appliqués pour conserver le bon fonctionnement des vues même si la session actuelle n'est pas dans le rôle de réplication par défaut.
Sélectionne l'index par défaut pour les prochaines opérations CLUSTER. La table n'est pas réorganisée.
Supprime de la table la spécification d'index CLUSTER la plus récemment utilisée. Cela agit sur les opérations de réorganisation suivantes qui ne spécifient pas d'index.
Cette forme ajoute une colonne système oid à la table (voir Section 5.4, « Colonnes système »). Elle ne fait rien si la table a déjà des OID.
Notez que ce n'est pas équivalent à ADD COLUMN oid oid; Ceci ajouterait une colonne normale qui s'appèlerait oid, mais ne serait pas la colonne système.
Supprime la colonne système oid de la table. Cela est strictement équivalent à DROP COLUMN oid RESTRICT, à ceci près qu'aucun avertissement n'est émis si la colonne oid n'existe plus.
Cette forme modifie un ou plusieurs paramètres de stockage pour la table. Voir ??? pour les détails sur les paramètres disponibles. Notez que le contenu de la table ne sera pas modifié immédiatement par cette commande ; suivant le paramètre, vous aurez besoin de réécrire la table pour obtenir les effets désirés. Ceci peut se faire avec CLUSTER ou une des formes d'ALTER TABLE qui force une réécriture de la table.
![[Note]](images/note.png)
Bien que CREATE TABLE autorise la spécification de OIDS avec la syntaxe WITH (paramètre_stockage), ALTER TABLE ne traite pas les OIDS comme un paramètre de stockage. À la place, utilisez les formes SET WITH OIDS et SET WITHOUT OIDS pour changer le statut des OID sur la table.
Cette forme réinitialise un ou plusieurs paramètres de stockage à leur valeurs par défaut. Comme avec SET, une réécriture de table pourrait être nécessaire pour mettre à jour entièrement la table.
Cette forme ajoute la table cible comme nouvel enfant à la table parent indiquée. En conséquence, les requêtes concernant le parent ajouteront les enregistrements de la table cible. Pour être ajoutée en tant qu'enfant, la table cible doit déjà contenir toutes les colonnes de la table parent (elle peut avoir des colonnes supplémentaires). Les colonnes doivent avoir des types qui correspondent, et s'il y a des contraintes NOT NULL défini pour le parent, alors elles doivent aussi avoir les contraintes NOT NULL pour l'enfant.
Il doit y avoir aussi une correspondance des contraintes de tables enfants pour toutes les contraintes CHECK. Actuellement, les contraintes UNIQUE, PRIMARY KEY et FOREIGN KEY ne sont pas prises en compte mais ceci pourrait changer dans le futur.
Cette forme supprime une table cible de la liste des enfants de la table parent indiquée. Les requêtes envers la table parent n'inclueront plus les enregistrements de la table cible.
Change le propriétaire d'une table, d'une séquence ou d'une vue. Le nouveau propriétaire est celui passé en paramètre.
Remplace le tablespace de la table par le tablespace spécifié et déplace le(s) fichier(s) de données associé(s) à la table vers le nouveau tablespace. Les index de la table, s'il y en a, ne sont pas déplacés ; mais ils peuvent l'être séparément à l'aide de commandes SET TABLESPACE supplémentaires. Voir aussi CREATE TABLESPACE.
Change le nom d'une table (d'un index, d'une séquence ou d'une vue) ou le nom d'une colonne individuelle de la table. Cela n'a aucun effet sur la donnée stockée.
Déplace la table dans un autre schéma. Les index, les contraintes et les séquences utilisées dans les colonnes de table sont également déplacés.
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.)
Le nom (éventuellement qualifié du nom du schéma) de la table à modifier. Si ONLY est indiqué avant le nom de la table, seule cette table est modifiée. Dans le cas contraire, la table et toutes ses tables filles (s'il y en a) sont modifiées. En option, * peut être ajouté après le nom de la table pour indiquer explicitement que les tables filles doivent être inclues.
Le nom d'une colonne, existante ou nouvelle.
Le nouveau nom d'une colonne existante.
Le nouveau nom de la table.
Le type de données de la nouvelle colonne, ou le nouveau type de données d'une colonne existante.
Une nouvelle contrainte de table pour la table.
Le nom d'une contrainte existante à supprimer.
Les objets qui dépendent de la colonne ou de la contrainte supprimée sont automatiquement supprimés (par exemple, les vues référençant la colonne).
La colonne ou la contrainte n'est pas supprimée si des objets en dépendent. C'est le comportement par défaut.
Le nom d'un déclencheur isolé à désactiver ou activer.
Désactiver ou activer tous les déclencheurs appartenant à la table. (Les droits de superutilisateur sont nécessaires si l'un des déclencheurs concerne une contrainte de clé étrangère.)
Désactiver ou activer tous les déclencheurs appartenant à la table à l'exception de ceux concernant des contraintes de clés étrangères.
Le nom de l'index sur lequel la table doit être réorganisée.
Le nom d'un paramètre de stockage de la table.
La nouvelle valeur d'un paramètre de stockage de la table. Cela peut être un nombre ou un mot suivant le paramètre.
Une table parent à associer ou dissocier de cette table.
Le nom du nouveau propriétaire de la table.
Le nom du tablespace où déplacer la table.
Le nom du schéma où déplacer la table.
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 ou supprimer une colonne système oid requiert de la même façon de réécrire la table entière.
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. (Ceci n'est pas vrai quand on supprime la colonne système oid ; ceci est fait avec une réécriture immédiate 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é. Seuls les ajouts de contraintes CHECK sont propagés, et c'est d'ailleurs obligatoire pour ces contraintes.
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.
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 SET DATA 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 et tous ses enfants :
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;
Pour enlever une contrainte check d'une table seule (pas sur ses enfants)
ALTER TABLE ONLY distributeurs DROP CONSTRAINT verif_cp;
(La contrainte check reste en place pour toutes les tables filles).
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;
Les formes ADD, DROP, SET DEFAULT et SET DATA TYPE (sans USING) 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
SERVER
