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
TOnouveau_nom_colonne
ALTER TABLE [ IF EXISTS ] [ ONLY ]nom
[ * ] RENAME CONSTRAINTnom_contrainte
TOnouveau_nom_contrainte
ALTER TABLE [ IF EXISTS ]nom
RENAME TOnouveau_nom
ALTER TABLE [ IF EXISTS ]nom
SET SCHEMAnouveau_schema
ALTER TABLE ALL IN TABLESPACEnom
[ OWNED BYnom_role
[, ... ] ] SET TABLESPACEnouveau_tablespace
[ NOWAIT ] ALTER TABLE [ IF EXISTS ]nom
ATTACH PARTITIONnom_partition
FOR VALUESspec_limite_partition
ALTER TABLE [ IF EXISTS ]nom
DETACH PARTITIONnom_partition
oùaction
peut être : ADD [ COLUMN ] [ IF NOT EXISTS ]nom_colonne
type
[ COLLATEcollation
] [contrainte_colonne
[ ... ] ] DROP [ COLUMN ] [ IF EXISTS ]nom_colonne
[ RESTRICT | CASCADE ] ALTER [ COLUMN ]nom_colonne
[ SET DATA ] TYPEtype
[ COLLATEcollation
] [ USINGexpression
] ALTER [ COLUMN ]nom_colonne
SET DEFAULTexpression
ALTER [ COLUMN ]nom_colonne
DROP DEFAULT ALTER [ COLUMN ]nom_colonne
{ SET | DROP } NOT NULL ALTER [ COLUMN ]nom_colonne
ADD GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY [ (options_sequence
) ] ALTER [ COLUMN ]nom_colonne
{ SET GENERATED { ALWAYS | BY DEFAULT } | SEToption_sequence
| RESTART [ [ WITH ]restart
] } [...] ALTER [ COLUMN ]nom_colonne
DROP IDENTITY [ IF EXISTS ] ALTER [ COLUMN ]nom_colonne
SET STATISTICSentier
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 } ADDcontrainte_table
[ NOT VALID ] ADDcontrainte_table_utilisant_index
ALTER CONSTRAINTnom_constrainte
[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ] VALIDATE CONSTRAINTnom_contrainte
DROP CONSTRAINT [ IF EXISTS ]nom_contrainte
[ RESTRICT | CASCADE ] DISABLE TRIGGER [nom_declencheur
| ALL | USER ] ENABLE TRIGGER [nom_declencheur
| ALL | USER ] ENABLE REPLICA TRIGGERnom_trigger
ENABLE ALWAYS TRIGGERnom_trigger
DISABLE RULEnom_regle_reecriture
ENABLE RULEnom_regle_reecriture
ENABLE REPLICA RULEnom_regle_reecriture
ENABLE ALWAYS RULEnom_regle_reecriture
DISABLE ROW LEVEL SECURITY ENABLE ROW LEVEL SECURITY FORCE ROW LEVEL SECURITY NO FORCE ROW LEVEL SECURITY CLUSTER ONnom_index
SET WITHOUT CLUSTER SET WITH OIDS SET WITHOUT OIDS SET TABLESPACEnouveau_tablespace
SET { LOGGED | UNLOGGED } SET (paramètre_stockage
[=valeur
] [, ... ] ) RESET (paramètre_stockage
[, ... ] ) INHERITtable_parent
NO INHERITtable_parent
OFnom_type
NOT OF OWNER TO {nouveau_propriétaire
| CURRENT_USER | SESSION_USER } REPLICA IDENTITY { DEFAULT | USING INDEXnom_index
| FULL | NOTHING } ettable_constraint_using_index
est: [ CONSTRAINTnom_contrainte
] { UNIQUE | PRIMARY KEY } USING INDEXnom_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.
ADD COLUMN [ IF NOT EXISTS ]
Ajoute une nouvelle colonne à la table en utilisant une syntaxe identique
à celle de CREATE TABLE. Si IF NOT
EXISTS
est précisée et qu'une colonne existe déjà avec ce nom,
aucune erreur n'est renvoyée.
DROP COLUMN [ IF EXISTS ]
Supprime une colonne de la table. Les index et les
contraintes de table référençant cette colonne sont automatiquement supprimés.
Les statistiques multivarées référençant les colonnes supprimées seront
également supprimées si la suppression de la colonne avait pour effet de
réduire le nombre de colonne dans la statistique à 1.
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.
SET DATA TYPE
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.
SET
/DROP DEFAULT
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.
SET
/DROP NOT NULL
Modifie l'autorisation de
valeurs NULL. SET NOT NULL
ne peut être utilisé que
si la colonne ne contient pas de valeurs NULL.
Si cette table est une partition, il n'est pas possible d'effectuer de
DROP NOT NULL
sur une colonne qui est marquée
NOT NULL
dans la table parente. Pour supprimer la
contrainte NOT NULL
de toutes les partitions,
effectuez un DROP NOT NULL
sur la table parente. Même
s'il n'y a pas de contrainte NOT NULL
sur la table
parente, une telle contrainte peut quand même être ajoutée à des
partitions individuelles, si l'on veut; ainsi, les enfants peuvent
refuser les valeurs nulles même si le parent les autorise, mais l'inverse
n'est pas possible.
ADD GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY
SET GENERATED { ALWAYS | BY DEFAULT }
DROP IDENTITY [ IF EXISTS ]
Modifier une colonne en colonne d'identité ou changer les attributs de génération d'ue colonne d'identité existante. Voir CREATE TABLE pour plus de détails.
Si DROP IDENTITY IF EXISTS
est spécifié et que la
colonne n'est pas une colonne d'identité, aucune erreur n'est remontée.
Dans ce cas une note est affichée à la place.
SET option_sequence
RESTART
Modifie la séquence associée à une colonne d'identité existante.
option_sequence
est une options supportée par
ALTER SEQUENCE tout comme INCREMENT
BY
.
SET STATISTICS
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.
SET STATISTICS
acquiert un verrou SHARE UPDATE EXCLUSIVE
.
SET ( attribute_option
= value
[, ... ] )
RESET ( attribute_option
[, ... ] )
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
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.
Changer les options d'une propriété nécessite un verrou
SHARE UPDATE EXCLUSIVE
.
SET STORAGE
SET STORAGE
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 67.2
pour plus d'informations.
ADD contrainte_table
[ NOT VALID ]
Ajoute une nouvelle contrainte à une table en utilisant une
syntaxe identique à CREATE TABLE, 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
nécessite un
verrou contrainte_table
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.
ADD table_constraint_using_index
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.
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.
ALTER CONSTRAINT
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.
VALIDATE CONSTRAINT
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.)
Cette commande récupère un verrou de type SHARE UPDATE
EXCLUSIVE
.
DROP CONSTRAINT [ IF EXISTS ]
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é.
DISABLE
/ENABLE [ REPLICA | ALWAYS ] TRIGGER
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
.
DISABLE
/ENABLE [ REPLICA | ALWAYS ] RULE
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.
DISABLE
/ENABLE ROW LEVEL SECURITY
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.
NO FORCE
/FORCE ROW LEVEL SECURITY
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.
CLUSTER ON
Sélectionne l'index par défaut pour les prochaines opérations CLUSTER. La table n'est pas réorganisée.
Changer les options de cluster nécessite un verrou SHARE UPDATE EXCLUSIVE
.
SET WITHOUT CLUSTER
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.
Changer les options de cluster nécessite un verrou SHARE UPDATE EXCLUSIVE
.
SET WITH OIDS
Cette forme ajoute une colonne système oid
à la table
(voir Section 5.4).
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.
SET WITHOUT OIDS
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.
SET TABLESPACE
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.
SET { LOGGED | UNLOGGED }
Cette clause modifie le statut journalisé/non journalisé d'une table
(voir UNLOGGED
). Cela ne peut pas
s'appliquer à une table temporaire.
SET ( paramètre_stockage
[= valeur
] [, ... ] )
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 ou une des formes
d'ALTER TABLE
qui force une réécriture de la table.
Pour les paramètres liés à l'optimiseur, les changement prendront effet
à partir de la prochaine fois que la table est verrouillée, donc les
requêtes en cours d'exécution ne seront pas affectées.
Un verrou de type SHARE UPDATE EXCLUSIVE
sera acquis
pour les paramètre de stockage fillfactor et autovacuum, ainsi que le
paramètre suivant lié à l'optimiseur :
parallel_workers
.
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.
RESET ( paramètre_stockage
[, ... ] )
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.
INHERIT table_parent
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.
NO INHERIT table_parent
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.
OF nom_type
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.
NOT OF
Cette forme dissocie une table typée de son type.
OWNER
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.
REPLICA IDENTITY
Cette forme change l'information écrite dans les journaux de transactions permettant d'identifier les lignes qui sont mises à jour ou supprimées. Dans la plupart des cas, l'ancienne valeur de chaque colonne ne sera enregistrée que si elle diffère de la nouvelle valeur ; néanmoins, si l'ancienne valeur est enregistrée extérieurement, elle est toujours tracée qu'elle soit modifiée ou pas. Cette option n'a pas d'effet quand la réplication logique est utilisée.
DEFAULT
Enregistre les anciennes valeurs de toutes les colonnes de la clé primaire, si elle existe. C'est la valeur par défaut pour les tables non systèmes.
USING INDEX nom_index
Enregistre les anciennes valeurs des colonnes couvertes par l'index
nommé, qui doit être d'unicité, non partiel, non déferrable, et
inclure seulement des colonnes marquées NOT NULL
.
Si cet index est supprimé, le comportement est identique à
NOTHING
.
FULL
Enregistre les anciennes valeurs de toutes les colonnes de la ligne.
NOTHING
N'enregistre aucune information sur l'ancienne ligne. C'est la valeur par défaut pour les tables systèmes.
RENAME
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.
SET SCHEMA
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.
ATTACH PARTITION nom_partition
FOR VALUES spec_limite_partition
Attache une table existante (qui peut elle-même être partitionnée) comme
une partition de la table cible en utilisant la même syntaxe pour
spec_limite_partition
que
CREATE TABLE. La spécification de limite de
partition doit correspondre à la stratégie de partitionnement et à la clé
de partition de la table cible. La table qui doit être attachée doit
avoir la totalité des colonnes identiques à la table cible et ne dois pas
en avoir plus; de plus, les types de colonnes doivent également
correspondre. De plus, elle doit avoir toutes les contraintes
NOT NULL
et CHECK
de la table
cible. Pour le moment, les contraintes UNIQUE
,
PRIMARY KEY
, et FOREIGN KEY
ne sont
pas considérées. Si une seule des contraintes CHECK
de la table étant attachée est marquée comme NO
INHERIT
, la commande échouera ; de telles contraintes doivent
être recréées sans la clause NO INHERIT
.
Si la nouvelle partition est une table standard, un parcours complet de
la table est effectué pour vérifier que les lignes existantes ne
violent pas la contrainte de partition. Il est possible d'éviter ce parcours
en ajoutant une contrainte CHECK
valide à la table qui
n'autoriserait que les lignes satisfaisant la contrainte de partition
désirée avant de lancer cette commande. La contrainte CHECK
sera utilisée pour déterminer si le parcours de la table est nécessaire pour valider la
contrainte de partition. Cependant, cela ne fonctionne pas si l'une des
clés de la partition est une expression et que la partition n'accepte pas
de valeurs NULL
. Si une partition de type liste qui
n'accepte pas de valeurs NULL
est attachée, ajoutez
également une contrainte NOT NULL
à la colonne de la
clé de partition, à moins qu'il s'agisse d'une expression.
Si la nouvelle partition est une table étrangère, rien ne sera fait pour vérifier que toutes les lignes de la table étrangères obéissent à la contrainte de partition. (Voir la discussion dans CREATE FOREIGN TABLE sur les contraintes sur les tables étrangères.)
DETACH PARTITION
nom_partition
Détache la partition spécifiée de la table cible. La partition détachée continue d'exister comme une table standard, mais n'a plus aucun lien avec la table dont elle vient d'être détachée.
Toutes les formes d'ALTER TABLE qui agissent sur une seule table, à
l'exception de RENAME
, SET SCHEMA
,
ATTACH PARTITION
, et DETACH PARTITION
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. De plus, pour attacher une table
en tant que nouvelle partition de la table, vous devez être le propriétaire
de la table qui est attachée. 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.
IF EXISTS
Ne renvoie pas une erreur si la table n'existe pas. Un message d'attention est renvoyé dans ce cas.
nom
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.
nom_colonne
Le nom d'une colonne, existante ou nouvelle.
nouveau_nom_colonne
Le nouveau nom d'une colonne existante.
nouveau_nom
Le nouveau nom de la table.
type_données
Le type de données de la nouvelle colonne, ou le nouveau type de données d'une colonne existante.
contraintedetable
Une nouvelle contrainte de table pour la table.
nomdecontrainte
Le nom d'une nouvelle contrainte ou d'une contrainte existante à supprimer.
CASCADE
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), ainsi que tous les objets dépendants de ces objets (voir Section 5.13).
RESTRICT
La colonne ou la contrainte n'est pas supprimée si des objets en dépendent. C'est le comportement par défaut.
nom_declencheur
Le nom d'un déclencheur isolé à désactiver ou activer.
ALL
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.)
USER
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.)
nomindex
Le nom d'un index existant.
paramètre_stockage
Le nom d'un paramètre de stockage de la table.
valeur
La nouvelle valeur d'un paramètre de stockage de la table. Cela peut être un nombre ou un mot suivant le paramètre.
table_parent
Une table parent à associer ou dissocier de cette table.
nouveau_propriétaire
Le nom du nouveau propriétaire de la table.
nouvel_espacelogique
Le nom du tablespace où déplacer la table.
nouveau_schema
Le nom du schéma où déplacer la table.
nom_partition
Le nom de la table à attacher comme nouvelle partition ou à détacher de cette table.
spec_limite_partition
La spécification de limite de partition pour une nouvelle partition. Se référer à CREATE TABLE pour plus de détails sur la syntaxe.
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.
Pareillement, quand une nouvelle partition est attachée elle pourrait être parcourue pour vérifier que les lignes existantes vérifient la contrainte de partition.
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 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 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 a des tables descendantes, il n'est pas permis d'ajouter,
renommer ou changer le type d'une colonne dans la table parente sans faire
la même chose sur tous les descendants. Cela permet de s'assurer que les
descendants ont toujours des colonnes qui correspondent au parent. De la
même façon, une contrainte ne peut pas être renommée dans la table parente
sans également la renommer dans tous les descendant, afin que toutes les
contraintes soient également en correspondance avec celles du parents et de
ses descendants. De plus, puisque la sélection de ligne de la table
parente sélectionne également des lignes de ses descendants, une contrainte
sur le parent ne peut pas être marquée comme valide à moins qu'elle ne le
soit également sur tous les descendants. Dans tous ces cas, ALTER
TABLE ONLY
sera rejeté.
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. Une commande DROP
COLUMN
non récursive échouera pour une table partitionnée,
puisque toutes les partitions d'une table doivent avoir les même colonnes
que la racine de partitionnement.
Les actions pour les colonnes d'identité (ADD GENERATED
,
SET
etc., DROP IDENTITY
), ainsi que
les actions TRIGGER
, CLUSTER
,
OWNER
, et TABLESPACE
ne sont jamais
appelées récursivement sur les 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 pour avoir une description plus complète des paramètres valides. Chapitre 5 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;
Attache une partition à une table partitionnée par intervalles :
ALTER TABLE measurement ATTACH PARTITION measurement_y2016m07 FOR VALUES FROM ('2016-07-01') TO ('2016-08-01');
Attache une partition à une table partitionnée par liste :
ALTER TABLE cities ATTACH PARTITION cities_ab FOR VALUES IN ('a', 'b');
Détache une partition d'une table partitionnée :
ALTER TABLE measurement DETACH PARTITION measurement_y2015m12;
Les formes ADD
(sans USING INDEX
),
DROP [COLUMN]
, DROP IDENTITY
, RESTART
,
SET DEFAULT
, SET DATA TYPE
(sans USING
),
SET GENERATED
, et SET
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 option_sequence
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.