| > > > | |
 ALTER SYSTEM
|
ALTER TABLESPACE
|
ALTER TABLE — Modifier la définition d'une table
ALTER TABLE [ IF EXISTS ] [ ONLY ] nom [ * ]
action [, ... ]
ALTER TABLE [ IF EXISTS ] [ ONLY ] nom [ * ]
RENAME [ COLUMN ] nom_colonne TO nouveau_nom_colonne
ALTER TABLE [ IF EXISTS ] [ ONLY ] nom [ * ]
RENAME CONSTRAINT nom_contrainte TO nouveau_nom_contrainte
ALTER TABLE [ IF EXISTS ] nom
RENAME TO nouveau_nom
ALTER TABLE [ IF EXISTS ] nom
SET SCHEMA nouveau_schema
ALTER TABLE ALL IN TABLESPACE nom [ OWNED BY nom_role [, ... ] ]
SET TABLESPACE nouveau_tablespace [ NOWAIT ]
où action peut être :
ADD [ COLUMN ] nom_colonne type [ COLLATE collation ] [ contrainte_colonne [ ... ] ]
DROP [ COLUMN ] [ IF EXISTS ] nom_colonne [ RESTRICT | CASCADE ]
ALTER [ COLUMN ] nom_colonne [ SET DATA ] TYPE type [ COLLATE collation ] [ USING expression ]
ALTER [ COLUMN ] nom_colonne SET DEFAULT expression
ALTER [ COLUMN ] nom_colonne DROP DEFAULT
ALTER [ COLUMN ] nom_colonne { SET | DROP } NOT NULL
ALTER [ COLUMN ] nom_colonne SET STATISTICS entier
ALTER [ COLUMN ] nom_column SET ( option_attribut = valeur [, ... ] )
ALTER [ COLUMN ] nom_column RESET ( option_attribut [, ... ] )
ALTER [ COLUMN ] nom_colonne SET STORAGE { PLAIN | EXTERNAL | EXTENDED | MAIN }
ADD contrainte_table [ NOT VALID ]
ADD contrainte_table_utilisant_index
ALTER CONSTRAINT nom_constrainte [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
VALIDATE CONSTRAINT nom_contrainte
DROP CONSTRAINT [ IF EXISTS ] 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
DISABLE ROW LEVEL SECURITY
ENABLE ROW LEVEL SECURITY
FORCE ROW LEVEL SECURITY
NO FORCE ROW LEVEL SECURITY
CLUSTER ON nom_index
SET WITHOUT CLUSTER
SET WITH OIDS
SET WITHOUT OIDS
SET TABLESPACE nouveau_tablespace
SET { LOGGED | UNLOGGED }
SET ( paramètre_stockage [= valeur] [, ... ] )
RESET ( paramètre_stockage [, ... ] )
INHERIT table_parent
NO INHERIT table_parent
OF nom_type
NOT OF
OWNER TO { nouveau_propriétaire | CURRENT_USER | SESSION_USER }
REPLICA IDENTITY { DEFAULT | USING INDEX nom_index | FULL | NOTHING }
et table_constraint_using_index est:
[ CONSTRAINT nom_contrainte ]
{ UNIQUE | PRIMARY KEY } USING INDEX nom_index
[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
ALTER TABLE modifie la définition d'une table existante. Il existe plusieurs variantes décrites après. Il est à noter que le niveau de verrouillage requis peut changer pour chaque variante. Un verrou ACCESS EXCLUSIVE est utilisé à moins que le verrou ne soit explicitement noté. Quand de multiples sous-commandes sont listées, le verrou utilisé sera celui le plus strict requis pour l'ensemble des sous-commandes.
Ajoute une nouvelle colonne à la table en utilisant une syntaxe identique à celle de CREATE TABLE(7).
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. Si IF EXISTS est indiqué et que la colonne n'existe pas, aucune erreur n'est renvoyée. Dans ce cas, un message d'avertissement est envoyé à la place.
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 COLLATE spécifie une collation pour la nouvelle colonne. Si elle est omise, la collation utilisée est la collation par défaut pour le nouveau type de la colonne. 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 et UPDATE suivantes ; elles ne modifient pas les lignes déjà présentes dans la table.
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(7)) 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 ».
SET STATISTICS acquiert un verrou SHARE UPDATE EXCLUSIVE.
Cette syntaxe permet de configurer ou de réinitialiser des propriétés. Actuellement, les seules propriétés acceptées sont n_distinct et n_distinct_inherited, qui surchargent l'estimation du nombre de valeurs distinctes calculée par ANALYZE(7) n_distinct affecte les statistiques de la table elle-même alors que n_distinct_inherited affecte les statistiques récupérées pour la table et les tables en héritant. Si configuré à une valeur positive, ANALYZE supposera que la colonne contient exactement le nombre spécifié de valeurs distinctes non NULL. Si configuré à une valeur négative qui doit être supérieur ou égale à -1, ANALYZE supposera que le nombre de valeurs distinctes non NULL dans la colonne est linéaire par rapport à la taille de la table ; le nombre total est à calculer en multipliant la taille estimée de la table par la valeur absolue de ce nombre. Par exemple, une valeur de -1 implique que toutes les valeurs dans la colonne sont distinctes alors qu'une valeur de -0,5 implique que chaque valeur apparaît deux fois en moyenne. Ceci peut être utile quand la taille de la table change dans le temps, car la multiplication par le nombre de lignes dans la table n'est pas réalisée avant la planification. Spécifiez une valeur de 0 pour retourner aux estimations standards du nombre de valeurs distinctes. Pour plus d'informations sur l'utilisation des statistiques par le planificateur de requêtes PostgreSQL™, référez vous à Section 14.2, « Statistiques utilisées par le planificateur ».
Changer les options d'une propriété nécessite un verrou SHARE UPDATE EXCLUSIVE.
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 63.2, « TOAST » pour plus d'informations.
Ajoute une nouvelle contrainte à une table en utilisant une syntaxe identique à CREATE TABLE(7), plus l'option NOT VALID, qui est actuellement seulement autorisée pour les contraintes de type clé étrangère et les contraintes CHECK.
Normalement, cette syntaxe provoquera un parcours de la table pour vérifier que toutes les lignes existantes de la table satisfont la nouvelle contrainte. Mais si l'option NOT VALID est utilisée, ce parcours potentiellement long est ignoré. La contrainte sera toujours appliquée pour les insertions ou mises à jour ultérieures (c'est-à-dire qu'elles échoueront sauf s'il existe une ligne correspondante dans la table référencée, dans le cas de clés étrangères, ou elles échoueront à moins que les nouvelles lignes correspondent à la condition de vérification spécifiée). Mais le la base de données ne supposera pas que la contrainte est valable pour toutes les lignes de la table, jusqu'à ce qu'elle soit validée à l'aide de la clause VALIDATE CONTRAINTE. Voir Notes ci-dessous pour plus d'informations sur l'utilisation de l'option NOT VALID.
Bien que la plupart des formes de ADD contrainte_table nécessite un verrou ACCESS EXCLUSIVE, ADD FOREIGN KEY nécessite seulement un verrou SHARE ROW EXCLUSIVE. Notez que ADD FOREIGN KEY nécessite aussi un verrou SHARE ROW EXCLUSIVE sur la table référencée, en plus du verrou sur la table où la contrainte est déclarée.
Cette forme ajoute une nouvelle contrainte PRIMARY KEY ou UNIQUE sur une table, basée sur un index unique existant auparavant. Toutes les colonnes de l'index sont incluses dans la contrainte.
Cet index ne peut pas être un index partiel, ni être sur des expressions de colonnes. De plus, il doit être un index b-tree avec un ordre de tri par défaut. Ces restrictions assurent que cet index soit équivalent à un index qui aurait été créé par une commande standard ADD PRIMARY KEY ou ADD UNIQUE.
Si vous précisez PRIMARY KEY, et que les colonnes de l'index ne sont pas déjà spécifiées comme NOT NULL, alors la commande va tenter d'appliquer la commande ALTER COLUMN SET NOT NULL sur chacune de ces colonnes. Cela nécessite un parcours complet de la table pour vérifier que la ou les colonne(s) ne contiennent pas de null. Dans tous les autres cas, c'est une opération rapide.
Si un nom de contrainte est fourni, alors l'index sera renommé afin de correspondre au nom de la contrainte. Sinon la contrainte sera nommée comme l'index.
Une fois que la commande est exécutée, l'index est « possédé » par la contrainte, comme si l'index avait été construit par une commande ADD PRIMARY KEY ou ADD UNIQUE ordinaire. En particulier, supprimer la contrainte fait également disparaître l'index.
![[Note]](../images/note.png)
Ajouter une contrainte en utilisant un index existant peut être utile dans les situations où il faut ajouter une nouvelle contrainte, sans bloquer les mises à jour de table trop longtemps. Pour faire cela, créez l'index avec CREATE INDEX CONCURRENTLY, puis installez-la en tant que contrainte officielle en utilisant cette syntaxe. Voir l'exemple ci-dessous.
Cette forme modifie les propriétés d'une contrainte précédemment créée. Pour le moment, seules les contraintes de clés étrangères peuvent être modifiées.
Cette forme valide une contrainte de type clé étrangère ou une contrainte CHECK qui a été précédemment créée avec la clause NOT VALID. Elle le fait en parcourant la table pour s'assurer qu'il n'existe aucune ligne pour laquelle la contrainte n'est pas satisfaite. Si la contrainte est déjà marquée valide, cette clause ne fait rien. (Voir Notes ci-dessous pour une explication de l'utilité de cette commande.)
Supprime la contrainte de table précisée. Si IF EXISTS est précisé et que la contrainte n'existe pas, aucune erreur n'est renvoyée. Par contre, un message d'avertissement est lancé.
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 générés en interne pour gérer les contraintes comme ceux utilisés pour implanter les contraintes de clés étrangères ou les contraintes déferrés uniques ou d'exclusion). Désactiver ou activer les déclencheurs implicites 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.
Cette commande acquiert un verrou SHARE ROW EXCLUSIVE.
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.
Ces clauses contrôlent l'application des politiques de sécurité de lignes appartenant à la table. Si activé et qu'aucune politique n'existe pour la table, alors une politique de refus est appliqué par défaut. Notez que les politiques peuvent exister pour une table même si la sécurité niveau ligne est désactivé. Dans ce cas, les politiques ne seront pas appliquées, elles seront ignorées. Voir aussi CREATE POLICY(7).
Ces clauses contrôlent l'application des politiques de sécurité niveau ligne appartenant à la table quand l'utilisateur est le propriétaire de la table. Si activé, les politiques de sécurité au niveau ligne seront appliquées quand l'utilisateur est le propriétaire de la table. S'il est désactivé (ce qui est la configuration par défaut), alors la sécurité niveau ligne ne sera pas appliquée quand l'utilisateur est le propriétaire de la table. Voir aussi CREATE POLICY(7).
Sélectionne l'index par défaut pour les prochaines opérations CLUSTER(7). La table n'est pas réorganisée.
Changer les options de cluster nécessite un verrou SHARE UPDATE EXCLUSIVE.
Supprime de la table la spécification d'index CLUSTER(7) la plus récemment utilisée. Cela agit sur les opérations de réorganisation suivantes qui ne spécifient pas d'index.
Changer les options de cluster nécessite un verrou SHARE UPDATE EXCLUSIVE.
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.
Ce n'est pas équivalent à ADD COLUMN oid oid. Cette dernière ajouterait une colonne normale nommée oid, qui n'est pas une 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 clause remplace le tablespace de la table par le tablespace indiqué, et déplace les fichiers 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 avec des commandes SET TABLESPACE séparées. Toutes les tables de la base de donnée d'un tablespace peuvent être déplacées en utilisant la clause ALL IN TABLESPACE, ce qui verrouillera toutes les tables pour les déplacer une par une. Cette clause supporte aussi OWNED BY, qui déplacera seulement les tables appartenant aux rôles spécifiées. Si l'option NOWAIT est précisée, alors la commande échouera si elle est incapable d'acquérir tous les verrous requis immédiatement. Notez que les catalogues systèmes ne sont pas déplacés par cette commande, donc utilisez ALTER DATABASE ou des appels explicites à ALTER TABLE si désiré. Les tables du schéma information_schema ne sont pas considérées comme faisant partie des catalogues systèmes et seront donc déplacées. Voir aussi CREATE TABLESPACE(7).
Cette clause modifie le statut journalisé/non journalisé d'une table (voir UNLOGGED). Cela ne peut pas s'appliquer à une table temporaire.
Cette forme modifie un ou plusieurs paramètres de stockage pour la table. Voir la section intitulée « Paramètres de stockage » pour les détails sur les paramètres disponibles. Le contenu de la table ne sera pas modifié immédiatement par cette commande ; en fonction du paramètre, il pourra s'avérer nécessaire de réécrire la table pour obtenir les effets désirés. Ceci peut se faire avec VACUUM FULL, CLUSTER(7) ou une des formes d'ALTER TABLE qui force une réécriture de la table.
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, utiliser 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, sauf pour celles qui ont été définies comme non-héritables (c'est-à-dire créées avec l'option ALTER TABLE ... ADD CONSTRAINT ... NO INHERIT) par la table parente, qui sont donc ignorées. Les contraintes des tables filles en correspondance avec celles de la table parente ne doivent pas être définies comme non-héritables. 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'incluront plus les enregistrements de la table cible.
Cette forme lie la table à un type composite comme si la commande CREATE TABLE OF l'avait créée. la liste des noms de colonnes et leurs types doit correspondre précisément à ceux du type composite ; il est permis de différer la présence d'une colonne système oid. . La table ne doit pas hériter d'une autre table. Ces restrictions garantissent que la commande CREATE TABLE OF pourrait permettre la définition d'une table équivalente.
Cette forme dissocie une table typée de son type.
Change le propriétaire d'une table, d'une séquence, d'une vue, d'une vue matérialisée ou d'une table distante. Le nouveau propriétaire est celui passé en paramètre.
Cette forme change l'information écrite dans les journaux de transactions permettant d'identifier les lignes qui sont mises à jour ou supprimées. Cette option n'a pas d'effet si la réplication logique n'est pas utilisée. DEFAULT (la valeur par défaut pour les tables non systèmes) enregistre les anciennes valeurs des colonnes de la clé primaire, s'il y en a une. USING INDEX enregistre les anciennes valeurs des colonnes couvertes par l'index, qui doit être de type unique, non partiel, non différée, et inclut uniquement les colonnes marquées NOT NULL. FULL enregistre les anciennes valeurs de toutes les colonnes de la ligne. NOTHING n'enregistre aucune information sur l'ancienne ligne. (Il s'agit de la valeur par défaut pour les tables systèmes). Dans tous les cas, aucune ancienne valeur ne sera enregistrée sauf si au moins une des colonnes qui aurait du être enregistrée présente des différences entre les anciennes et les nouvelles versions de la ligne.
Change le nom d'une table (ou d'un index, d'une séquence, d'une vue, d'une vue matérialisée ou d'une table distante) ou le nom d'une colonne individuelle de la table ou le nom d'une contrainte 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 formes d'ALTER TABLE qui agissent sur une seule table, à l'exception de RENAME et SET SCHEMA, 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 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 ou le tablespace 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 recréation de la table. Néanmoins, un superutilisateur peut modifier le propriétaire de n'importe quelle table.) Pour ajouter une colonne ou modifier un type de colonne ou utiliser la clause OF, vous devez avoir le droit USAGE sur le type de la donnée.
Ne renvoie pas une erreur si la table n'existe pas. Un message d'attention est renvoyé dans ce cas.
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 nouvelle contrainte ou 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 est un déclencheur interne pour la gestion d'une contrainte comme ceux utilisés pour implanter les contraintes de type clés étrangères ou les contraintes déferrables comme les contraintes uniques et d'exclusion.)
Désactiver ou activer tous les déclencheurs appartenant à la table sauf les déclencheurs systèmes permettant de gérer en interne certaines contraintes, comme celles utilisées pour implanter les contraintes de type clés étrangères ou les contraintes déferrables comme les contraintes uniques et d'exclusion.)
Le nom d'un index existant.
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). S'il n'y a pas de clause DEFAULT, il s'agit seulement d'un changement de métadonnée qui ne nécessite pas une mise à jour immédiate des données de la table ; les valeurs NULL ajoutées sont fournies lors de la lecture, à la place.
AJouter une colonne avec une clause DEFAULT ou changer le type d'une colonne existante nécessitera la réécriture de la totalité de la table ainsi que de ses index. La seule exception sur le changement de type d'une colonne existante est si la clause USING ne change pas le contenu de la colonne et que l'ancien type est soit transformable de façon binaire dans le nouveau type, ou bien un domaine sans contrainte reposant sur le nouveau type, alors il n'est pas nécessaire de réécrire la table, mais tous les index sur les colonnes affectées doivent quand même être reconstruits. Le fait d'ajouter ou de supprimer une colonne système oid nécessite également une réécriture complète de la table. Les reconstructions de table et/ou d'index peuvent prendre un temps significatif pour une grosse table, et peuvent nécessiter temporairement de doubler l'espace disque utilisé.
Ajouter une contrainte CHECK ou NOT NULL requiert de parcourir la table pour vérifier que les lignes existantes respectent cette contrainte, mais ne requiert par une ré-écriture de la table.
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.
Parcourir une grosse table pour vérifier une nouvelle clé étrangère ou une nouvelle contrainte de vérification peut prendre beaucoup de temps, alors que d'autres mises à jour de la table sont verrouillées le temps que la commande ALTER TABLE ADD CONSTRAINT ne soit validée. Le but principal de l'option de contrainte NOT VALID est de réduire l'impact de l'ajout d'une constrainte sur les mises à jour concurrentes. Avec NOT VALID, la commande ADD CONSTRAINT ne parcourt pas la table et peut être validée immédiatement. Après cela, une commande VALIDATE CONSTRAINT peut être exécutée pour vérifier que les lignes existantes satisfont la contrainte. L'étape de validation n'a pas besoin de verrouiller les mises à jour concurrentes car PostgreSQL sait que les autres transactions seront forcées de respecter la contrainte pour les lignes qu'elles insèrent ou mettent à jour. Seules les lignes pré-existantes doivent être vérifiées. De ce fait, la validation récupère seulement un verrou SHARE UPDATE EXCLUSIVE sur la table en cours de modification. (Si la contrainte est une clé étrangère, alors un verrou ROW SHARE est aussi requis sur la table référencée par la contrainte.) De plus, pour améliorer les accès concurrents, il peut être utile d'utiliser NOT VALID et VALIDATE CONSTRAINT pour les cas où la table est connue pour contenir des violations pré-existantes. Une fois la contrainte en place, aucune ligne en violation ne peut être insérée, et les problèmes existants peuvent être corrigés à loisir jusqu'à ce que VALIDATE CONSTRAINT réussisse enfin.
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.)
Pour forcer une réécriture immédiate de la table, vous pouvez utiliser VACUUM FULL, CLUSTER(7) ou bien une des formes de la commande ALTER TABLE qui force une réécriture. Ceci ne cause pas de modifications visibles dans la table, mais élimine des données qui ne sont plus utiles.
Les formes d'ALTER TABLE qui ré-écrivent la table ne sont pas sûres au niveau MVCC. Après une ré-écriture de la table, elle apparaitra vide pour les transactions concurrentes si elles ont utilisé une image de la base prise avant la ré-écriture de la table. Voir Section 13.5, « Avertissements » pour plus de détails.
L'option USING de SET DATA 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 SET DATA 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, SET DATA 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 ou de renommer une contrainte héritée 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 contraintes CHECK sont propagées, et uniquement si elles ne sont pas marquées NO INHERIT.
Tout changement sur une table du catalogue système est interdit.
Voir la commande CREATE TABLE(7) 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 truc
ALTER COLUMN truc_timestamp SET DATA TYPE timestamp with time zone
USING
timestamp with time zone 'epoch' + truc_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 truc
ALTER COLUMN truc_timestamp DROP DEFAULT,
ALTER COLUMN truc_timestamp TYPE timestamp with time zone
USING
timestamp with time zone 'epoch' + truc_timestamp * interval '1 second',
ALTER COLUMN truc_timestamp SET DEFAULT now();
Renommer une colonne existante :
ALTER TABLE distributeurs RENAME COLUMN adresse TO ville;
Renommer une table existante :
ALTER TABLE distributeurs RENAME TO fournisseurs;
Pour renommer une contrainte existante :
ALTER TABLE distributeurs RENAME CONSTRAINT verif_cp TO verif_code_postal;
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);
Pour ajouter une contrainte CHECK à une table, mais pas à ses filles :
ALTER TABLE distributeurs ADD CONSTRAINT verif_cp CHECK (char_length(code_postal) = 5) NO INHERIT;
(The check constraint will not be inherited by future children, either.)
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);
Pour ajouter une contrainte de clé étrangère à une table avec le moins d'impact sur le reste de l'activité
ALTER TABLE distributeurs ADD CONSTRAINT distfk FOREIGN KEY (address) REFERENCES adresses (adresse) NOT VALID; ALTER TABLE distributeurs VALIDATE CONSTRAINT distfk;
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;
Recréer une contrainte de clé primaire sans bloquer les mises à jour pendant la reconstruction de l'index :
CREATE UNIQUE INDEX CONCURRENTLY dist_id_temp_idx ON distributeurs (dist_id);
ALTER TABLE distributeurs DROP CONSTRAINT distributeurs_pkey,
ADD CONSTRAINT distributeurs_pkey PRIMARY KEY USING INDEX dist_id_temp_idx;
Les formes ADD (without USING INDEX), 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.