CREATE TABLE — Définir une nouvelle table
CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXISTS ]nom_table
( [ {nom_colonne
type_donnees
[ STORAGE { PLAIN | EXTERNAL | EXTENDED | MAIN | DEFAULT } ] [ COMPRESSIONméthode_compression
] [ COLLATEcollation
] [contrainte_colonne
[ ... ] ] |contrainte_table
| LIKEtable_source
[option_like
... ] } [, ... ] ] ) [ INHERITS (table_parent
[, ... ] ) ] [ PARTITION BY { RANGE | LIST | HASH } ( {nom_colonne
| (expression
) } [ COLLATEcollation
] [opclass
] [, ... ] ) ] [ USINGmethode
] [ WITH (parametre_stockage
[=valeur
] [, ... ] ) | WITHOUT OIDS ] [ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ] [ TABLESPACEnom_tablespace
] CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLEnom_table
OFnom_type
[ ( {nom_colonne
[ WITH OPTIONS ] [contrainte_colonne
[ ... ] ] |contrainte_table
} [, ... ] ) ] [ PARTITION BY { RANGE | LIST | HASH } ( {nom_colonne
| (expression
) } [ COLLATEcollation
] [opclass
] [, ... ] ) ] [ USINGmethode
] [ WITH (storage_parameter
[=value
] [, ... ] ) | WITHOUT OIDS ] [ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ] [ TABLESPACEnom_tablespace
] CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXISTS ]table_name
PARTITION OFparent_table
[ ( {nom_colonne
[ WITH OPTIONS ] [contrainte_colonne
[ ... ] ] |table_constraint
} [, ... ] ) ] { FOR VALUESspec_limites_partition
| DEFAULT } [ PARTITION BY { RANGE | LIST } ( {nom_colonne
| (expression
) } [ COLLATEcollation
] [opclass
] [, ... ] ) ] [ USINGmethode
] [ WITH (parametre_stockage
[=valeur
] [, ... ] ) | WITHOUT OIDS ] [ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ] [ TABLESPACEnom_tablespace
] oùcontrainte_colonne
peut être : [ CONSTRAINTnom_contrainte
] { NOT NULL | NULL | CHECK (expression
) [ NO INHERIT ] | DEFAULTexpression_par_défaut
| GENERATED ALWAYS AS (expr_generation
) STORED | GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY [ (options_sequence
) ] | UNIQUE [ NULLS [ NOT ] DISTINCT ]paramètres_index
| PRIMARY KEYparametres_index
| EXCLUDE [ USINGmethode_index
] (élément_exclude
WITHopérateur
[, ... ] )paramètres_index
[ WHERE (prédicat
) ] | REFERENCEStable_reference
[ (colonne_reference
) ] [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ] [ ON DELETEaction_referentielle
] [ ON UPDATEaction_referentielle
] } [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ] etoption_like
peut valoir : { INCLUDING | EXCLUDING } { COMMENTS | COMPRESSION | CONSTRAINTS | DEFAULTS | GENERATED | IDENTITY | INDEXES | STATISTICS | STORAGE | ALL } andspec_limites_partition
is: IN (expr_limite_partition
[, ...] ) | FROM ( {expr_limite_partition
| MINVALUE | MAXVALUE } [, ...] ) TO ( {expr_limite_partition
| MINVALUE | MAXVALUE } [, ...] ) | WITH ( MODULUSlitéral_numérique
, REMAINDERlitéral_numérique
) etcontrainte_table
: [ CONSTRAINTnom_contrainte
] { CHECK (expression
) [ NO INHERIT ] | UNIQUE [ NULLS [ NOT ] DISTINCT ] (nom_colonne
[, ... ] )paramètres_index
| PRIMARY KEY (nom_colonne
[, ... ] )parametres_index
| EXCLUDE [ USINGméthode_index
] (élément_exclude
WITHopérateur
[, ... ] )paramètres_index
[ WHERE (prédicate
) ] | FOREIGN KEY (nom_colonne
[, ... ] ) REFERENCEStable_reference
[ (colonne_reference
[, ... ] ) ] [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ] [ ON DELETEaction_referentielle
] [ ON UPDATEaction_referentielle
] } [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ] Lesparamètres_index
dans les contraintesUNIQUE
,PRIMARY KEY
etEXCLUDE
sont : [ INCLUDE (nom_colonne
[, ... ] ) ] [ WITH (paramètre_stockage
[=valeur
] [, ... ] ) ] [ USING INDEX TABLESPACEnom_tablespace
]élément_exclude
dans une contrainteEXCLUDE
peut valoir : {nom_colonne
| (expression
) } [ COLLATEcollation
] [opclass
[ (parametre_opclass
=valeur
[, ... ] ) ] ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ]referential_action
dans une contrainteFOREIGN KEY
/REFERENCES
vaut : { NO ACTION | RESTRICT | CASCADE | SET NULL [ (nom_colonne
[, ... ] ) ] | SET DEFAULT [ (nom_colonne
[, ... ] ) ] }
CREATE TABLE
crée une nouvelle table initialement vide
dans la base de données courante. La table appartient à
l'utilisateur qui exécute cette commande.
Si un nom de schéma est donné (par exemple, CREATE TABLE monschema.matable ...
),
alors la table est créée dans le schéma spécifié.
Dans le cas contraire, elle est créée dans le schéma courant. Les tables temporaires existent
dans un schéma spécial, il n'est donc pas nécessaire de fournir un nom de schéma
lors de la création d'une table temporaire. Le nom de la table doit être distinct
du nom des autres relations (table, séquence, index, vue, vue matérialisée ou table distante)
dans le même schéma.
CREATE TABLE
crée aussi automatiquement un type de
données qui représente le type composé correspondant à une ligne de la
table. Ainsi, les tables doivent avoir un nom distinct de tout type de
données du même schéma.
Les clauses de contrainte optionnelles précisent les contraintes (ou tests) que les nouvelles lignes ou les lignes mises à jour doivent satisfaire pour qu'une opération d'insertion ou de mise à jour réussisse. Une contrainte est un objet SQL qui aide à définir l'ensemble des valeurs valides de différentes façons.
Il existe deux façons de définir des contraintes : celles de table et celles de colonnes. Une contrainte de colonne fait partie de la définition de la colonne. Une définition de contrainte de tables n'est pas liée à une colonne particulière et peut englober plusieurs colonnes. Chaque contrainte de colonne peut être écrite comme une contrainte de table ; une contrainte de colonne n'est qu'un outil de notation utilisé lorsque la contrainte n'affecte qu'une colonne.
Pour pouvoir créer une table, vous devez avoir le droit
USAGE
sur les types de chaque colonne ou sur le type
indiqué dans la clause OF
.
TEMPORARY
ou TEMP
#
La table est temporaire. Les tables temporaires sont automatiquement
supprimées à la fin d'une session ou, optionnellement, à la fin de la
transaction en cours (voir ON COMMIT
ci-dessous). Le
paramètre search_path par défaut inclut tout d'abord le schéma
temporaire et donc les tables permanentes existantes nommées de façon
identiques ne sont pas choisies pour les nouveaux plans tant que la
table temporaire existe sauf s'il y est fait référence par leur nom
qualifié du schéma. Tous les index créés sur une table temporaire sont
automatiquement temporaires.
Le démon autovacuum ne peut pas
accéder et, du coup, ne peut pas exécuter un VACUUM ou un ANALYZE sur
les tables temporaires. Pour cette raison, les opérations VACUUM et
ANALYZE doivent être traitées via des commandes SQL de session. Par
exemple, si une table temporaire doit être utilisée dans des requêtes
complexes, il est raisonnable d'exécuter ANALYZE
sur
la table temporaire après qu'elle ait été peuplée.
On peut éventuellement écrire GLOBAL
ou LOCAL
avant TEMPORARY
ou TEMP
. Cela
ne fait pas de différence dans PostgreSQL (cf.
Cela ne fait actuellement pas de différence dans PostgreSQL
et est obsolète ; voir Compatibilité).
UNLOGGED
#Si spécifié, la table est créée en tant que table non tracée. Les données écrites dans ce type de table ne sont pas écrites dans les journaux de transactions (voir Chapitre 30), ce qui les rend considérablement plus rapides que les tables ordinaires. Néanmoins, elles ne sont pas sûres en cas d'arrêt brutal : une table non tracée est automatiquement vidée après un arrêt brutal. Le contenu d'une table non tracée n'est pas répliqué vers les serveurs en attente. Tout index créé sur une table non tracée est aussi automatiquement non tracé.
Si c'est indiqué, toute séquences créée avec une table non journalisée
(pour une colonne d'identité ou pour une colonne de type serial
)
est aussi créée en tant que non journalisée.
IF NOT EXISTS
#N'affiche pas d'erreur si une relation de même nom existe déjà. Un message de niveau notice est retourné dans ce cas. Notez qu'il n'existe aucune garantie que la relation existante ressemble à celle qui devait être créée..
nom_table
#Le nom (éventuellement qualifié du nom du schéma) de la table à créer.
OF nom_type
#
Crée une table typée, qui prend sa structure à
partir du type composite spécifié (son nom peut être qualifié du schéma).
Une table typée est liée à son type ; par exemple, la table sera
supprimée si le type est supprimé (avec DROP TYPE ...
CASCADE
).
Quand une table typée est créée, les types de données des colonnes sont
déterminés par le type composite sous-jacent et ne sont pas indiqués
par la commande CREATE TABLE
. Mais la commande
CREATE TABLE
peut ajouter des valeurs par défaut et
des contraintes à la table. Elle peut aussi indiquer des paramètres de
stockage.
nom_colonne
#Le nom d'une colonne de la nouvelle table.
type_données
#Le type de données de la colonne. Cela peut inclure des spécificateurs de tableaux. Pour plus d'informations sur les types de données supportés par PostgreSQL, on se référera à Chapitre 8.
COLLATE collation
#
La clause COLLATE
affecte un collationnement à une
colonne (qui doit être d'un type de données collationnable). Sans
information, le collationnement par défaut du type de données de la
colonne est utilisé.
STORAGE { PLAIN | EXTERNAL | EXTENDED | MAIN | DEFAULT }
#
Cette syntaxe permet de configurer le mode de stockage pour la colonne.
Ceci contrôle si la colonne est contenue dans la table principale ou dans
la table secondaire appelée TOAST, et si les données
doivent être compressées ou non. PLAIN
doit être utilisé pour les valeurs à longueur fixe tels que
integer
et est sur la table principale, non compressée.
MAIN
est utilisée pour placer la donnée sur la table
principale, avec des données potentiellement compressées.
EXTERNAL
s'utilise pour un stockage externe, sans
compression, alors que EXTENDED
est pour un
stockage externe avec compression.
Écrire DEFAULT
configure le mode de stockage au mode
par défaut pour le type de données de la colonne.
EXTENDED
est la valeur par défaut pour la plupart des
types de données qui acceptent un stockage non-PLAIN
.
L'utilisation d'EXTERNAL
rend plus rapide les opérations
sur les sous-chaînes de valeurs text
et bytea
particulièrement grosses, mais avec un espace de stockage accru.
Voir Section 73.2 pour plus d'informations.
COMPRESSION méthode_compression
#
La clause COMPRESSION
configure la méthode de
compression pour la colonne. La compression est supportée uniquement
pour les types de données de taille variable, et est utilisée
uniquement quand le mode de stockage de la colonne est
main
ou extended
.(voir ALTER TABLE pour des informations sur les modes de
stockage des colonnes.) Configurer cette propriété sur une table
partitionnée n'a pas d'effet direct car de telles tables n'ont pas de
stockage propre, mais la valeur configurée sera héritée par les
nouvelles partitions. Les méthodes de compression supportées sont
pglz
et lz4
.
(lz4
est disponible seulement si
--with-lz4
a été utilisé lors de la compilation de
PostgreSQL.) De plus, méthode_compression
peut valoir
default
pour indiquer explicitement le comportement
par défaut, qui est de consulter la configuration du paramètre default_toast_compression au moment de l'insertion des
données pour déterminer la méthode à utiliser.
INHERITS ( table_parent
[, ... ])
#
La clause optionnelle INHERITS
indique une liste de
tables dont les colonnes sont automatiquement héritées par la nouvelle
table. Les tables parents peuvent être des tables standards ou des
tables distantes.
L'utilisation d'INHERITS
crée une relation
persistante entre la nouvelle table enfant et sa table parent. Les
modifications de schéma du(des) parent(s) se propagent normalement aux
enfants et, par défaut, les données de la table enfant sont incluses
dans les parcours de(s) parent(s).
Si un même nom de colonne existe dans plusieurs tables parentes, une erreur est rapportée, à moins que les types de données des colonnes ne correspondent dans toutes les tables parentes. S'il n'y a pas de conflit, alors les colonnes dupliquées sont assemblées pour former une seule colonne dans la nouvelle table. Si la liste des noms de colonnes de la nouvelle table contient un nom de colonne hérité, le type de données doit correspondre à celui des colonnes héritées et les définitions des colonnes sont fusionnées. Si la nouvelle table spécifie explicitement une valeur par défaut pour la colonne, cette valeur surcharge toute valeur par défaut héritée. Dans le cas contraire, les parents qui spécifient une valeur par défaut doivent tous spécifier la même, sans quoi une erreur est rapportée.
Les contraintes CHECK
sont fusionnées, dans les
grandes lignes, de la même façon que les colonnes : si des tables
parentes multiples et/ou la nouvelle définition de table contient des
contraintes CHECK
de même nom, ces contraintes
doivent toutes avoir la même expression de vérification, ou une erreur
sera retournée. Les contraintes qui ont le même nom et la même
expression seront fusionnées en une seule. Une contrainte marquée
NO INHERIT
dans une table parent ne sera pas prise en
compte. Notez qu'une contrainte CHECK
non nommée dans
la nouvelle table ne sera jamais fusionnée puisqu'un nom unique lui sera
toujours affecté.
Les paramètres STORAGE
de la colonne sont aussi
copiés des tables parents.
Si une colonne de la table parente est une colonne d'identité, cette propriété n'est pas héritée. Une colone dans la table enfant peut être déclarée comme colonne d'identité si l'on veut.
PARTITION BY { RANGE | LIST | HASH } ( { nom_colonne
| ( expression
) } [ opclass
] [, ...] )
#
La clause facultative PARTITION BY
spécifie une
statégie pour partitionner la table. La table ainsi crée est appelée
table partitionnée. La liste de colonnes ou
d'expressions entre parenthèses forme la clé de
partitionnement de la table. Quand un partitionnement par
intervalle ou hachage est utilisé, la clé de partitionnement peut
inclure de multiples colonnes ou expressions (jusqu'à 32, mais cette
limite peut être modifiée lors de la compilation de
PostgreSQL.), mais pour le partitionnement
par liste, la clé de partitionnement doit être constituée d'une seule
colonne ou expression.
Les partitionnements par intervalle ou par liste nécessitent une classe d'opérateur btree, alors que le partitionnement par hachage exige une classe d'opérateur hash. Si aucune classe d'opérateur n'est précisée explicitement, la classe d'opérateur par défaut du type approprié sera utilisée ; si aucune classe d'opérateur n'existe, une erreur sera levée. Si le partitionnement par hachage est utilisé, la classe d'opérateur utilisée doit implémenter la fonction de support 2 (voir Section 38.16.3 pour les détails).
Une table partitionnée est divisée en sous tables (appelées partitions),
qui sont créées en utilisant des commandes CREATE
TABLE
séparées. La table partitionnée est elle-même vide.
Une ligne de données insérée dans la table est redirigée vers une
partition en fonction de la valeur des colonnes ou expressions de la clé
de partitionnement. S'il n'existe pas de partition correspondant aux
valeurs de la nouvelle ligne, une erreur sera levée.
Les tables partitionnées ne supportent pas les contraintes
EXCLUDE
; cependant vous pouvez définir ces
contraintes sur des partitions individuelles.
Voir Section 5.11 pour plus de détails sur le partitionnement des tables.
PARTITION OF table_parent
{ FOR VALUES spec_limites_partition
| DEFAULT }
#
Crée la table comme une partition de la table
parente spécifiée. La table peut être créée, soit comme une partition
pour des valeurs spécifiques avec FOR VALUES
, soit
comme la partition par défaut avec DEFAULT
. Tout
index, toute contrainte et tout trigger de niveau ligne défini par
l'utilisateur existant dans la table parent est clonée sur la nouvelle
partition.
Le paramètre spec_limites_partition
doit correspondre
à la méthode et à la clé de partitionnement de la table parent, et ne
doit pas déborder sur toute partition existante du parent. La forme avec
IN
est utilisée pour le partitionnement de liste,
la forme avec FROM
et TO
est
utilisée pour le partitionnement par intervalles, et la forme avec
WITH
est utilisée pour le partitionnement par hachage.
expr_limite_partition
peut
être n'importe quelle expression sans variable (les sous-requêtes, fonctions
de fenêtrage, fonctions d'aggrégation ou toute fonction renvoyant une
relation ne sont pas autorisées). Son type de donnée doit être le même
que celui de la clé de partitionnement. L'expression étant évaluée une
seule fois, au moment de la création de la table, il est possible d'utiliser
des expressions volatiles comme
.
CURRENT_TIMESTAMP
Lors de la création d'une partition en liste, NULL
peut être indiquer pour signifier que la partition permet à la colonne
de clé de partitionnement d'être NULL. Néanmoins, il ne peut y avoir
plus d'une partition de ce type pour une table parent donnée.
NULL
n'est pas accepté pour les partitions par
intervalle.
Lorsqu'une partition de type intervalle est créée, la borne inférieure
spécifiée avec FROM
est une borne inclusive, alors
que la borne supérieure spécifiée avec TO
est une
borne exclusive. C'est-à-dire que les valeurs spécifiées dans la liste
FROM
sont des valeurs valides des colonnes
correspondantes de la clé de partitionnement pour cette partition, alors
que celles dans la liste TO
ne le sont pas. Notez que
ceci doit être compris suivant les règles de la comparaison de lignes
(Section 9.24.5). Par exemple, étant donné
PARTITION BY RANGE (x,y)
, une limite de partition
FROM (1, 2) TO (3, 4)
permet x=1
pour tout y>=2
, x=2
avec tout
y
non NULL, et x=3
avec tout
y<4
.
Les valeurs spéciales MINVALUE
et
MAXVALUE
peuvent être utilisées lors de la création
d'une partition par intervalles pour indiquer qu'il n'y a pas de limite
basse ou haute sur la valeur de la colonne. Par exemple, une partition
définie comme utilisant FROM (MINVALUE) TO (10)
accepte toutes les valeurs inférieures à 10, et une partition définie en
utilisant FROM (10) TO (MAXVALUE)
accepte toutes les
valeurs supérieures ou égales à 10.
Lors de la création d'une partition par intervalles impliquant plus d'une
colonne, il est aussi sensé d'utiliser MAXVALUE
comme
élément de la limite basse et MINVALUE
comme élément
de limite haute. Par exemple, une partition définie en utilisant
FROM (0, MAXVALUE) TO (10, MAXVALUE)
accepte toute
ligne où la première colonne de la clé de partitionnement est supérieure
à zéro et inférieure ou égale à dix. De la même façon, une partition
définie en utilisant FROM ('a', MINVALUE) TO ('b',
MINVALUE)
accepte toute ligne où la première colonne de la clé
de partitionnement commence avec la lettre a.
Notez que si MINVALUE
ou MAXVALUE
est utilisé pour une colonne d'une limite de partitionnement, la même
valeur doit être utilisée pour toutes les colonnes suivantes. Par
exemple, (10, MINVALUE, 0)
n'est pas une limite
valide. Vous devriez écrire (10, MINVALUE, MINVALUE)
.
De plus, notez que certains types d'éléments, tels que
timestamp
, ont une notion d'infinité, qui est
simplement une autre valeur qui peut être enregistré. Ceci est différent
de MINVALUE
et MAXVALUE
, qui ne
sont pas de vraies valeurs pouvant être enregistrées, mais plutôt une
façon de dire que la valeur est sans limite. MAXVALUE
peut être vu comme étant supérieur à toute autre valeur, ceci incluant
infinity et MINVALUE
comme étant inférieure à toute
autre valeur, ceci incluant moins infinity. De ce fait, l'intervalle
FROM ('infinity') TO (MAXVALUE)
n'est pas un
intervalle vide. Il autorise le stockage d'une seule valeur --
"infinity".
Quand une partition par liste de valeurs est créée,
NULL
peut être spécifié pour dire que la partition
autorise la colonne de la clé de partitionnement à être NULL. Cepdnant,
il ne peut pas y avoir plus d'une partition par liste de ce type pour une
même table parente. NULL
ne peut pas être utilisé
pour les partitions par intervalles.
Si DEFAULT
est spécifié, la table sera créée comme la
partition par défaut de la table parente. Celle option n'est pas
disponible pour les tables partitionnées par hachage. Une clé de
partition qui ne passe dans aucune autre partition de la table parente
sera orientée vers la partition par défaut.
Si une table possède une partition DEFAULT
et qu'on
lui ajoute une nouvelle partition, la partition par défaut
doit être parcourue pour vérifier qu'elle ne contient aucune ligne
qui appartient normalement à la nouvelle partition. Si la partition par
défaut contient un grand nombre de lignes, cela peut être long. Ce
parcours peut être évité si la partition par défaut est une table
étrangère ou possède une contrainte prouvant qu'elle ne peut contenir
des lignes qui devraient appartenir à la nouvelle partition.
À la création d'une partition par hachage, un diviseur et un reste doivent être spécifiés. Le diviseur doit être un entier positif, et le reste un entier non négatif inférieur au diviseur. Typiquement, au début de la mise en place d'un partitionnement par hachage, vous devrez choisir un diviseur égal au nombre de partitions et assigner à chaque table le même diviseur et un reste différent (voir les exemples plus bas). Cependant, il n'est pas obligatoire que chaque partition ait le même diviseur, juste que chaque diviseur apparaissant dans une table partitionnée par hachage soit un facteur du diviseur immédiatement supérieur. Cela permet d'augmenter le nombre de partitions de manière incrémentale sans avoir besoin de déplacer toutes les données d'un coup. Par exemple, supposons que vous ayez une table partitionnée par hachage avec 8 partitions, toutes de diviseur 8, mais que vous trouvez qu'il faille augmenter le nombre de partitions à 16. Vous pouvez détacher une des partitions de diviseur 8, créer deux nouvelles partitions de diviseur 16 couvrant la même partie de l'espace des clés (une avec un reste égal au reste de la partition détachée, l'autre avec un reste de cette valeur plus 8), et les peupler avec les données. Vous pouvez répéter ceci -- peut-être plus tard -- pour chaque partition de diviseur 8 jusqu'à ce qu'il n'y en ait plus. Bien que cela implique de grands mouvements de données à chaque étape, c'est toujours mieux qu'avoir à créer toute une nouvelle table et d'avoir à déplacer toutes les données en une seule fois.
Une partition doit avoir les mêmes noms de colonne et types de données
que la table partitionnée à laquelle elle appartient. Toute modification
du type ou du nom d'une colonne d'une table partitionnée sera
automatiquement propagée à toutes les partitions. Les contraintes de
type CHECK
seront automatiquement héritées pour
chaque partition, mais il est possible de définir des contraintes de
type CHECK
supplémentaires sur certaines partitions.
Si une contrainte sur une partition porte le même nom qu'une contrainte
ajoutée sur la table mère, elle sera fusionnée avec la contrainte de la
table parent. Il est possible de définir des valeurs par défaut
différentes pour chaque partition. Notez que la valeur par défaut d'une
partition ne s'applique pas quand l'insertion de la ligne se fait via la
table partitionnée.
Les lignes insérées dans une table partitionnées seront automatiquement redirigées vers la bonne partition. Si aucune des partitions existantes ne convient, une erreur sera levée.
Les opérations telles que TRUNCATE
qui n'affectent normalement une table
ainsi que tous ses enfants hérités seront cascadées sur toutes les
partitions, mais peuvent aussi être effectuées sur une partition
individuelle.
Notez que créer une partition en utilisant PARTITION OF
nécessite de prendre un verrou ACCESS EXCLUSIVE
sur la
table partitionnée parente. De même, supprimer une partition avec
DROP TABLE
nécessite de prendre un verrou
ACCESS EXCLUSIVE
sur la table parente. Il est possible
d'utiliser ALTER TABLE
ATTACH/DETACH PARTITION
pour réaliser ces opérations avec
un verrou plus faible, réduisant ainsi les interférences avec les
opérations concurrentes sur la table partitionnée.
LIKE table_source
[ option_like
... ]
#
La clause LIKE
spécifie une table à partir de laquelle
la nouvelle table copie automatiquement tous les noms de colonnes, leur
types de données et les contraintes non NULL.
Contrairement à INHERITS
, la nouvelle table et la
table originale sont complètement découplées à la fin de la création.
Les modifications sur la table originale ne sont pas appliquées à la
nouvelle table et les données de la
nouvelle table sont pas prises en compte lors du parcours de l'ancienne table.
De plus, contrairement à INHERITS
, les
colonnes et les contraintes copiées par LIKE
ne sont
pas assemblées avec des colonnes et des contraintes nommées de façon
similaire. Si le même nom est indiqué explicitement ou dans une autre
clause LIKE
, une erreur est rapportée.
La clause facultative like_option
permet
de spécifier quelle propriété supplémentaire de la table originale
doit être copiée. Spécifier INCLUDING
copie la propriété,
spécifier EXCLUDING
ne copie pas la propriété.
EXCLUDING
est la valeur par défaut. Si plusieurs clauses
INCLUDING
et EXCLUDING
sont indiquées
pour le même type d'objet, la dernière clause est utilisée. Les options
disponibles sont :
INCLUDING COMMENTS
#Les commentaires sur les colonnes, contraintes et index seront copiés. Le comportement par défaut exclue les commentaires, ce qui fait que les colonnes, les contraintes et les index de la nouvelle table n'ont pas de commentaire.
INCLUDING COMPRESSION
#La méthode de compression des colonnes sera copiée. Le comportement par défaut est d'exclure les méthodes de compression, résultant en des colonnes ayant la méthode de compression par défaut.
INCLUDING CONSTRAINTS
#
Les contraintes de type CHECK
seront copiées.
Aucune distinction ne sera faite entre les contraintes de colonnes
et les contraintes de table. Tout contrainte de non nullité est
systématiquement recopiée sur la nouvelle table.
INCLUDING DEFAULTS
#
Les valeurs par défaut sur les colonnes seront recopiées
dans la nouvelle table. Si cette clause n'est pas ajoutée, les
valeurs par défaut ne sont pas recopiées, ce qui fait que les
colonnes de la nouvelle table n'ont pas de valeur par défaut. Il
est important de noter que copier une valeur par défaut qui fait appel
à une fonction qui peut modifier la base de données, comme la fonction
nextval
, peut créer un lien entre la table
originale et les nouvelles tables.
INCLUDING GENERATED
#Toute définition d'une colonne générée sera copiée. Par défaut, les nouvelles colonnes copiées à partir de colonnes générées sont de simples colonnes normales.
INCLUDING IDENTITY
#Toute spécification d'une colonne d'identité sera copiée. Une nouvelle séquence est créée pour chaque colonne d'identité de la nouvelle table, séparément des séquences associées à la table d'origine.
INCLUDING INDEXES
#
Les index, les contraintes de type PRIMARY KEY
,
UNIQUE
et EXCLUDE
de la table
d'origine seront créés sur la nouvelle table. Les noms pour les
nouveaux index et les nouvelles contraintes sont choisis automatiquement
suivant des règles fixes, quel que soit leur nom sur
la table d'origine. (Ce comportement permet d'éviter d'essayer de créer
un index portant un nom déjà utilisé.)
INCLUDING STATISTICS
#Les statistiques étendues sont copiées sur la nouvelle table.
INCLUDING STORAGE
#
Les spécifications de STORAGE
sont copiées sur
la nouvelle colonne. Le comportement par défaut exclue la clause de
STORAGE
, ce qui fait que, par défaut, les colonnes
de la nouvelle table n'ont pas de définition particulière pour le stockage.
Pour plus d'informations sur la clause STORAGE
, voir
Section 73.2.
INCLUDING ALL
#
INCLUDING ALL
permet de sélectionner toutes les
options individuelles ci-dessus d'un seul coup. (Il peut être utile
d'ajouter des options individuelles EXCLUDING
après
une clause INCLUDING ALL
pour sélectionner toutes
les options sauf certaines.)
La clause LIKE
peut aussi être utilisée pour copier les
définitions de colonne des vues, tables distantes et types composites. Les options
inapplicables (comme INCLUDING INDEXES
à partir d'une vue)
sont ignorées.
CONSTRAINT nom_contrainte
#
Le nom optionnel d'une contrainte de colonne ou de table. Si la contrainte
est violée, le nom de la contrainte est présente dans les messages d'erreur.
Donc les noms de contraintes comme col doit être positive
peut être utilisés pour communiquer des informations utiles aux applications
clients. (Des doubles guillemets sont nécessaires pour indiquer les noms des
contraintes qui contiennent des espaces.) Si un nom de contrainte n'est pas
donné, le système en crée un.
NOT NULL
#Interdiction des valeurs NULL dans la colonne.
NULL
#Les valeurs NULL sont autorisées pour la colonne. Comportement par défaut.
Cette clause n'est fournie que pour des raisons de compatibilité avec les bases de données SQL non standard. Son utilisation n'est pas encouragée dans les nouvelles applications.
CHECK ( expression
) [ NO INHERIT ]
#
La clause CHECK
spécifie une expression de
résultat booléen que les nouvelles lignes ou celles mises à jour
doivent satisfaire pour qu'une opération d'insertion ou de mise à jour
réussisse. Les expressions de résultat TRUE ou UNKNOWN réussissent.
Si une des lignes de l'opération d'insertion ou de mise à jour produit un résultat
FALSE, une exception est levée et la base de données n'est pas modifiée.
Une contrainte de vérification sur une colonne ne fait référence qu'à la valeur de la colonne
tandis qu'une contrainte sur la table fait référence à plusieurs colonnes.
Actuellement, les expressions CHECK
ne peuvent ni
contenir des sous-requêtes ni faire référence à des variables autres que les
colonnes de la ligne courante (voir Section 5.4.1). La colonne système tableoid
peut être référencé contrairement aux autres colonnes systèmes.
Une contrainte marquée NO INHERIT
ne sera pas propagée
aux tables filles.
Quand une table a plusieurs contraintes CHECK
, elles
seront testées pour chaque ligne dans l'ordre alphabétique de leur nom,
après la vérification des contraintes NOT NULL
. (Les
versions de PostgreSQL antérieures à la 9.5
ne respectaient pas d'ordre de déclenchement particulier pour les
contraintes CHECK
.)
DEFAULT
expression_par_défaut
#
La clause DEFAULT
, apparaissant dans la définition
d'une colonne, permet de lui affecter une valeur par défaut. La valeur est une
expression libre de variable (en particulier, les références croisées aux
autres colonnes de la table courante ne sont pas autorisées). Subqueries
are not allowed either. Le type de
données de l'expression par défaut doit correspondre au type de données de
la colonne.
L'expression par défaut est utilisée dans les opérations d'insertion qui ne spécifient pas de valeur pour la colonne. S'il n'y a pas de valeur par défaut pour une colonne, elle est NULL.
GENERATED ALWAYS AS ( generation_expr
) STORED
#Cette clause crée la colonne comme une colonne générée. La colonne n'est pas accessible en écriture. À la lecture de cette colonne, l'expression spécifiée calculée sera renvoyée.
Le mot-clé STORED
est nécessaire pour indiquer que
la colonne sera calculée lors de l'écriture et sera stockée sur le disque.
L'expression de génération peut faire référence à d'autres colonnes de la table, mais pas à une autre colonne générée. Toute fonction ou opérateur utilisé dans l'expression de génération devra être IMMUTABLE. Toute référence à d'autres tables est interdite.
GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY [ ( options_sequence
) ]
#
Cette clause crée la colonne comme une colonne
d'identité. Elle aura une séquence implicite attachée à elle
et la colonne dans les nouvelles lignes auront automatiquement des
valeurs récupérées de la séquence qui lui est assignée. Une telle
colonne est implicitement NOT NULL
.
Les clauses ALWAYS
et BY DEFAULT
déterminent comment la valeur de la séquence est prioritaire par rapport
à une valeur définie par l'utilisateur dans un ordre
INSERT
et UPDATE
.
Pour un ordre INSERT
, si ALWAYS
est
spécifié, une valeur définie par l'utilisateur ne sera acceptée que si l'ordre
INSERT
spécifie OVERRIDING SYSTEM
VALUE
. Si BY DEFAULT
est spécifié, alors la
valeur spécifiée par l'utilisateur est prioritaire. Voir INSERT
pour plus de détails. (Avec une commande COPY
,
les valeurs spécifiées par l'utilisateur sont toujours utilisées quelque soit ce paramètre).
Pour un ordre UPDATE
, si ALWAYS
est
spécifié, toute mise à jour de la colonne à une valeur autre que
DEFAULT
sera rejetée. Si BY
DEFAULT
est spécifié, la colonne peut être mise à jour normalement.
(Il n'y a pas de clause OVERRIDING
pour les ordres
UPDATE
).
La clause facultative options_sequence
peut
être utilisée pour surcharger les options d'une séquence. Voir CREATE SEQUENCE pour plus de détails.
UNIQUE [ NULLS [ NOT ] DISTINCT ]
(column constraint)UNIQUE [ NULLS [ NOT ] DISTINCT ] ( nom_colonne
[, ... ] )
[ INCLUDE ( nom_colonne
[, ...])
] (contrainte de table) #
La contrainte UNIQUE
indique qu'un groupe d'une ou
plusieurs colonnes d'une table ne peut contenir que des valeurs uniques.
Le comportement de la contrainte d'unicité de table est le même que
celle de la contrainte d'unicité de colonne, avec la capacité
supplémentaire de traiter plusieurs colonnes. Dans ce cas, la contrainte
s'assure que tout couple de ligne diffère au moins sur une de ces
colonnes.
Pour une contrainte d'unicité, les valeurs NULL ne sont pas considérées
comme égales, sauf si la clause NULLS NOT DISTINCT
est précisée.
Chaque contrainte d'unicité doit nommer un ensemble de colonnes qui est différent de l'ensemble de colonnes nommées par toute autre contrainte d'unicité ou de clé primaire définie pour la table. (Sinon les contraintes d'unicité redondantes seraient ignorées.)
Lors de la mise en place d'une contrainte unique sur une hiérarchie de partitions à plusieurs niveaux, toutes les colonnes de la clé de partitionnement de la table partitionnée cible, ainsi que celles des tables partitionnées filles, doivent être incluses dans la définition de la contrainte.
Ajouter une contrainte d'unicité va automatiquement créer un index btree unique sur la colonne ou le groupe de colonnes utilisée(s) dans la contrainte.
La clause optionnelle INCLUDE
ajoute à cet index une
ou plusieurs colonnes qui sont uniquement une
« charge » : l'unicité n'est pas forcée pour elle, et
l'index ne peut pas être utilisé dans une recherche sur ces colonnes.
Cependant, elles peuvent être récupérées par un parcours d'index seul.
Notez cependant que si la contrainte n'est pas appliquée sur ces
colonnes incluses, elle en dépend tout de même. En conséquence,
certaines opérations sur ces colonnes (par exemple DROP
COLUMN
) peuvent causer une suppression en cascade de la
contrainte et de l'index.
PRIMARY KEY
(contrainte de colonne)PRIMARY KEY ( nom_colonne
[, ... ] )
[ INCLUDE ( nom_colonne
[, ...])
] (contrainte de table) #
La contrainte PRIMARY KEY
indique qu'une ou plusieurs
colonnes d'une table peuvent uniquement contenir des valeurs uniques
(pas de valeurs dupliquées) et non NULL. Une table ne peut avoir qu'une
seule clé primaire, que ce soit une contrainte au niveau de la colonne
ou au niveau de la table.
La contrainte clé primaire doit nommer un ensemble de colonnes différent de l'ensemble de colonnes nommé par toute contrainte unique définie sur la même table. (Sinon, la contrainte unique est redondante et sera ignorée.)
PRIMARY KEY
force les mêmes contraintes sur les
données que la combinaison UNIQUE
et NOT
NULL
. Néanmoins, identifier un ensemble de colonnes comme une
clé primaire fournit aussi des métadonnées sur la conception du schéma
car une clé primaire implique que les autres tables peuvent s'appuyer
sur cet ensemble de colonnes comme un identifiant unique des lignes de
la table.
Lors de leur ajout sur une table partitionnée, les contraintes
PRIMARY KEY
partagent les restrictions des
contraintes UNIQUE
précédemment décrites.
Ajouter une contrainte PRIMARY KEY
créera
automatiquement un index btree d'unicité sur la colonne ou le groupe de
colonnes utilisées dans la contrainte.
Ajouter une contrainte PRIMARY KEY
va automatiquement
créer un index btree unique sur la colonne ou le groupe de colonnes
utilisée(s) dans la contrainte.
La clause supplémentaire INCLUDE
ajoute à l'index une
ou plusieurs colonnes qui sont une simple « charge »:
l'unicité n'est pas contrainte pour ces colonnes, et l'index ne peut pas
être utilisé sur la base de ces colonnes. Néanmoins, elles peuvent être
récupérées par un parcours d'index seul. Notez que, bien que la
contrainte n'est pas imposée sur les colonnes incluses, elle dépend des
colonnes. En conséquence, certaines opérations sur ces colonnes (par
exemple DROP COLUMN
) peuvent causer la suppression en
cascade de la contrainte et de l'index.
EXCLUDE [ USING méthode_index
] ( élément_exclusion
WITH opérateur
[, ... ] ) paramètres_index
[ WHERE ( prédicat
) ]
#
La clause EXCLUDE
définit une contrainte d'exclusion
qui garantit que si deux lignes sont comparées sur la ou les colonnes
spécifiées ou des expressions utilisant le ou les opérateurs spécifiés,
seulement certaines de ces comparaisons, mais pas toutes, renverront
TRUE
. Si tous les opérateurs spécifiés testent une
égalité, ceci est équivalent à une contrainte UNIQUE
bien qu'une contrainte unique ordinaire sera plus rapide. Néanmoins, ces
contraintes d'exclusion peuvent spécifier des contraintes qui sont plus
générales qu'une simple égalité. Par exemple, vous pouvez spécifier
qu'il n'y a pas deux lignes dans la table contenant des cercles de
surcharge (voir Section 8.8) en utilisant
l'opérateur &&
. Le(s) opérateur(s) doivent
être commutatif(s).
Des contraintes d'exclusion sont implantées en utilisant un index,
donc chaque opérateur précisé doit être associé avec une classe
d'opérateurs appropriée (voir Section 11.10) pour la
méthode d'accès par index, nommée méthode_index
.
Chaque élément_exclusion
définit une colonne
de l'index, donc il peut spécifier en option une collation, une classe
d'opérateur, des paramètres pour la classe d'opérateurs, et/ou des
options de tri ; toutes ces options sont décrites entièrement dans
CREATE INDEX.
La méthode d'accès doit supporter amgettuple
(voir
Chapitre 64) ; dès à présent, cela signifie que
GIN ne peut pas être utilisé. Bien que cela soit
autorisé, il existe peu de raison pour utiliser des index B-tree ou hash
avec une contrainte d'exclusion parce que cela ne fait rien de mieux que
ce que peut faire une contrainte unique ordinaire. Donc, en pratique,
la méthode d'accès sera toujours GiST ou
SP-GiST.
Le prédicat
vous permet de
spécifier une contrainte d'exclusion sur un sous-ensemble de la
table ; en interne, un index partiel est créé. Notez que ces
parenthèses sont requis autour du prédicat.
REFERENCES table_reference
[ ( colonne_reference
) ]
[ MATCH type_correspondance
]
[ ON DELETE action_referentielle
]
[ ON UPDATE action_referentielle
]
(contrainte de colonne)FOREIGN KEY ( nom_colonne
[, ... ] )
REFERENCES table_reference
[ (
colonne_reference
[, ... ] ) ]
[ MATCH matchtype
]
[ ON DELETE action
]
[ ON UPDATE action
]
(contrainte de colonne) #
Ces clauses spécifient une contrainte de clé étrangère. Cela signifie
qu'un groupe de colonnes de la nouvelle table ne peut contenir que des
valeurs correspondant à celles des colonnes de référence de la table de
référence. Si la liste colonne_reference
est omise, la clé
primaire de la table_reference
est utilisée.
Sinon, la liste refcolumn
doit faire référence aux colonnes d'une contrainte d'unicité ou de clé
primaire non déferrable d'un index d'unicité non partiel.
L'utilisateur doit avoir la permission REFERENCES
sur
la table référencée (soit toute la table, ou la colonne référencée
spécifiquement). L'ajout d'une contrainte de type clé étrangère requiert
un verrou SHARE ROW EXCLUSIVE
sur la table
référencée. Les contraintes de type clé étrangère ne peuvent pas être
définies entre des tables temporaires et des tables permanentes.
Une valeur insérée dans les colonnes de la nouvelle table est comparée aux
valeurs des colonnes de référence dans la table de référence à l'aide du
type de concordance fourni. Il existe trois types de correspondance :
MATCH FULL
(NDT : correspondance totale),
MATCH PARTIAL
(NDT : correspondance partielle) et
MATCH SIMPLE
(NDT : correspondance simple), qui
est aussi la valeur par défaut. MATCH FULL
n'autorise
une colonne d'une clé étrangère composite à être NULL que si l'ensemble
des colonnes de la clé étrangère sont NULL. Si elles sont NULL, la ligne
n'a pas besoin d'avoir une correspondance dans la table référencée.
MATCH SIMPLE
permet à n'importe quel colonne d'une clé
étrangère d'être NULL ; si l'une d'entre elles est NULL, la ligne
n'a pas besoin d'avoir une correspondance dans la table référencée.
MATCH PARTIAL
n'est pas encore implémentée. Bien sûr,
les contraintes NOT NULL
peuvent être appliquées sur
la (ou les) colonne(s) référençantes pour empêcher ces cas de survenir.
Lorsque les données des colonnes référencées sont modifiées,
des actions sont réalisées sur les données de la table référençant.
La clause ON DELETE
spécifie l'action à
réaliser lorsqu'une ligne référencée de la table de référence est supprimée.
De la même façon, la clause ON UPDATE
spécifie l'action à réaliser lorsqu'une colonne référencée est mise à jour.
Si la ligne est mise à jour sans que la valeur de la colonne référencée ne soit
modifiée, aucune action n'est réalisée. Les actions référentielles autres
que la vérification NO ACTION
ne peuvent
pas être différées même si la contrainte est déclarée retardable.
Les actions suivantes sont possibles pour chaque clause :
NO ACTION
#Une erreur est produite pour indiquer que la suppression ou la mise à jour entraîne une violation de la contrainte de clé étrangère. Si la contrainte est différée, cette erreur est produite au moment de la vérification, si toutefois il existe encore des lignes de référence. C'est le comportement par défaut.
RESTRICT
#
Une erreur est produite pour indiquer que la suppression ou la mise à jour
entraîne une violation de la contrainte de clé étrangère. Ce comportement
est identique à NO ACTION
, si ce n'est que la vérification
n'est pas décalable dans le temps.
CASCADE
#La mise à jour ou la suppression de la ligne de référence est propagée à l'ensemble des lignes qui la référencent, qui sont, respectivement, mises à jour ou supprimées.
SET NULL [ ( nom_colonne
[, ... ] ) ]
#
Initialise toutes les colonnes référençantes ou un sous-ensemble
indiqué des colonnes référençantes à NULL. Un sous-ensemble de
colonnes peut seulement être référencé pour des actions ON
DELETE
.
SET DEFAULT [ ( nom_colonne
[, ... ] ) ]
#
Initialise toutes les colonnes référençantes ou un sous-ensemble
indiqué des colonnes référençantes à leur valeur par défaut. Un
sous-ensemble de colonnes peut seulement être indiqué pour les actions
ON DELETE
. (Il doit exister une ligne dans la
table référencée correspondant aux valeurs par défaut, si elles ne
sont pas NULL. Dans le cas contraire, l'opération échouera.)
Si les colonnes référencées sont modifiées fréquemment, il est conseillé d'ajouter un index sur les colonnes référençantes pour que les actions associées à la contrainte de clé étrangère soient plus performantes.
DEFERRABLE
NOT DEFERRABLE
#
Ces clauses contrôlent la possibilité de différer la contrainte. Une contrainte qui
n'est pas décalable dans le temps est vérifiée immédiatement après chaque commande. La
vérification des contraintes décalables est repoussée à la
fin de la transaction (à l'aide de la commande
SET CONSTRAINTS
).
NOT DEFERRABLE
est la valeur par défaut.
Actuellement, seules les contraintes UNIQUE
,
PRIMARY KEY
, EXCLUDE
et
REFERENCES
(clé étrangère) acceptent cette clause.
Les contraintes NOT NULL
et CHECK
ne sont pas diferrables. Notez que les contraintes différables ne
peuvent pas être utilisées comme arbitres d'un conflit dans une commande
INSERT
qui inclut une clause ON CONFLICT DO
UPDATE
.
INITIALLY IMMEDIATE
INITIALLY DEFERRED
#
Si une contrainte est décalable dans le temps, cette clause précise le
moment de la vérification. Si la contrainte est
INITIALLY IMMEDIATE
, elle est vérifiée après chaque
instruction. Si la contrainte est INITIALLY DEFERRED
,
elle n'est vérifiée qu'à la fin de la transaction. Le moment de
vérification de la contrainte peut être modifié avec la commande
SET CONSTRAINTS
.
USING method
#
Cette clause facultative permet de spécifier la méthode d'accès utilisée
pour stocker le contenu de la nouvelle table. La méthode doit être une
méthode d'accès de type TABLE
. Voir Chapitre 63
pour plus d'informations. Si cette option n'est pas indiquée, la méthode
d'accès par défaut est choisie pour la nouvelle table. Voir default_table_access_method pour plus d'informations.
WITH ( paramètre_stockage
[= valeur
] [, ... ] )
#
Cette clause spécifie les paramètres de stockage optionnels pour une
table ou un index ; voir Paramètres de stockage pour plus d'informations.
Pour la rétro-compatibilité de la clause WITH
d'une table, il est possible de spécifier OIDS=FALSE
pour indiquer que les lignes de la nouvelle table ne doivent pas contenir
d'OID (identifiants d'objets). OIDS=TRUE
n'est plus
supporté.
WITHOUT OIDS
#
Il s'agit de la syntaxe rétro-compatible pour déclarer une table
sans utiliser d'OID (identifiants d'objets), il n'est plus possible
de créer une table avec l'option WITH OIDS
.
ON COMMIT
#
Le comportement des tables temporaires à la fin d'un bloc de transactions
est contrôlé à l'aide de la clause ON COMMIT
. Les trois
options sont :
PRESERVE ROWS
#Aucune action n'est entreprise à la fin des transactions. Comportement par défaut.
DELETE ROWS
#
Toutes les lignes de la table temporaire sont détruites à la fin de
chaque bloc de transactions. En fait, un TRUNCATE
automatique
est réalisé à chaque validation.
Lorsque cette clause est utilisée sur une table partitionnée, elle
n'est pas exécutée en cascade sur ses partitions.
DROP
#La table temporaire est supprimée à la fin du bloc de transaction. Lorsque cette clause est utilisée sur une table partitionnée, cette action supprime les partitions et. Quand elle est utilisée sur une table ayant des tables filles, ces dernières sont aussi supprimées.
TABLESPACE nom_tablespace
#
nom_tablespace
est le
nom du tablespace dans lequel est créée la nouvelle table. S'il
n'est pas spécifié, default_tablespace est consulté, sauf
si la table est temporaire auquel cas temp_tablespaces
est utilisé. Pour les tables partitionnées, comme la clause de stockage n'a pas
de sens pour la table en elle-même, le tablespace indiqué écrase le
default_tablespace
comme tablespace par défaut à utiliser pour toute
nouvelle partition à moins qu'un autre tablespace soit explicitement indiqué.
USING INDEX TABLESPACE nom_tablespace
#
Les index associés à une contrainte UNIQUE
,
PRIMARY KEY
, ou EXCLUDE
sont créés dans le tablespace
nommé nom_tablespace
.
S'il n'est pas précisé, default_tablespace est consulté, sauf
si la table est temporaire auquel cas temp_tablespaces
est utilisé.
La clause WITH
spécifie des paramètres de
stockage pour les tables ainsi que pour les index associés
avec une contrainte UNIQUE
, PRIMARY
KEY
, ou EXCLUDE
. Les paramètres de stockage
des index sont documentés dans CREATE INDEX. Les
paramètres de stockage actuellement disponibles pour les tables sont
listés ci-dessous. Pour beaucoup de ces paramètres, comme indiqué, il y a
un paramètre additionnel, de même nom mais préfixé par
toast.
, qui contrôle le le comportement de la table
TOAST (stockage supplémentaire), si elle existe (voir
Section 73.2 pour plus d'informations sur TOAST). Si
une valeur de paramètre d'une table est configuré et que le paramètre
équivalent toast.
ne l'est pas, la partie TOAST
utilisera la valeur du paramètre de la table. Ces paramètres ne sont pas
supportés sur les tables partitionnées mais vous pouvez les indiquer sur
des partitions enfants individuelles.
fillfactor
(integer
)
#
Le facteur de remplissage d'une table est un pourcentage entre 10 et 100.
100 (paquet complet) est la valeur par défaut. Quand un facteur de
remplissage plus petit est indiqué, les opérations
INSERT
remplissent les pages de table d'au maximum ce
pourcentage ; l'espace restant sur chaque page est réservé à la mise
à jour des lignes sur cette page. Cela donne à UPDATE
une chance de placer la copie d'une ligne mise à jour sur la même page
que l'original, ce qui est plus efficace que de la placer sur une page
différente, et rend les mises à jour
heap-only tuple plus probables. Pour une table dont les entrées
ne sont jamais mises à jour, la valeur par défaut est le meilleur choix,
mais pour des tables mises à jour fréquemment, des facteurs de
remplissage plus petits sont mieux appropriés. Ce paramètre n'est pas
disponible pour la table TOAST.
toast_tuple_target
(integer
)
#
toast_tuple_target
spécifie la taille de tuple
minimale requise avant de tenter de compresser et/ou déplacer les champs
de grande taille vers des tables TOAST, et est aussi la taille cible à
laquelle l'on tente de réduire la taille une fois cette opération
démarrée. Cela affecte les colonnes marquées External (pour le
déplacement), Main (pour la compression) ou Extended (pour les deux) et
ne s'applique qu'aux nouveaux enregistrements. Cela n'a pas d'effet sur
les lignes existantes. Par défaut ce paramètre est configuré pour
permettre au moins 4 lignes par bloc, ce qui donnera 2040 octets
avec la taille de bloc par défaut. Les valeurs valides sont entre
128 octets et (taille des blocs - entête), par défaut
8160 octets. Changer cette valeur n'est pas très utile pour les
lignes très courtes ou très longues. Notez que la valeur par défaut est
souvent proche de la valeur optimale, et qu'il est possible que modifier
ce paramètre ait des effets négatifs dans certains cas. Ce paramètre ne
peut être positionné pour les tables TOAST.
parallel_workers
(integer
)
#Ce paramètre configure le nombre de processus pouvant être utilisés pour aider lors d'un parcours parallélisé de cette table. Si ce paramètre n'est pas configuré, le système déterminera une valeur en se basant sur la taille de la relation. Le nombre réel de processus choisis par le planificateur ou par des instructions utilitaires qui utilisent des parcours séquentiels pourrait être moindre, par exemple suite à la configuration de max_worker_processes.
autovacuum_enabled
, toast.autovacuum_enabled
(boolean
)
#
Active ou désactive le démon autovacuum pour une table particulière. Si
elle vaut true, le démon autovacuum réalise des
VACUUM
et/ou ANALYZE
automatiques
sur cette table en suivant les règles discutées dans Section 25.1.6.
À false, cette table ne sera pas traitée par le
démon autovacuum, sauf s'il y a un risque de réutilisation des
identifiants de transaction. Voir Section 25.1.5 pour plus d'informations sur la
prévention de ce problème. Notez que le démon autovacuum n'est pas
lancé (sauf pour prévenir la réutilisation des identifiants de
transaction) si le paramètre autovacuum vaut
false ; configurer les paramètres de stockage d'une table ne
surcharge pas cela. De ce fait, il y a peu d'intérêt de configurer ce
paramètre à true
.
vacuum_index_cleanup
, toast.vacuum_index_cleanup
(enum
)
#
Force ou désactive le nettoyage des index quand un
VACUUM
est lancé sur cette table. La valeur par
défaut est AUTO
. À OFF
, le
nettoyage de l'index est désactivé. À ON
, il est
activé. À AUTO
, une décision est prise
dynamiquement, chaque fois que VACUUM
s'exécute. Le
comportement dynamique permet à VACUUM
d'éviter un
parcours inutile des index pour supprimer très peu de lignes mortes.
Forcer la désactivation du nettoyage de tous les index peut accélérer
très significativement une opération VACUUM
, mais
pourrait résulter en des index sévèrement fragmentés si les
modifications de la table sont fréquentes. Le paramètre
INDEX_CLEANUP
de la commande VACUUM
, si indiqué,
surcharge la valeur de cette option.
vacuum_truncate
, toast.vacuum_truncate
(boolean
)
#
Active ou désactive la suppression par le vacuum des pages vides en fin
de table. La valeur par défaut est true
. Si
true
, la commande VACUUM
et
l'autovacuum suppriment ces pages et l'espace libéré est redonné
au système d'exploitation. La suppression de ces pages vides nécessite
un verrou de type ACCESS EXCLUSIVE
sur la table.
Le paramètre TRUNCATE
de la commande
VACUUM
,
si indiqué, écrase cette valeur.
autovacuum_vacuum_threshold
, toast.autovacuum_vacuum_threshold
(integer
)
#Valeur spécifique à la table pour le paramètre autovacuum_vacuum_threshold. Ce paramètre peut être configuré pour les tables partitionnées.
autovacuum_vacuum_scale_factor
, toast.autovacuum_vacuum_scale_factor
(floating point
)
#Valeur spécifique à la table pour le paramètre autovacuum_vacuum_scale_factor. Ce paramètre peut être configuré pour les tables partitionnées.
autovacuum_vacuum_insert_threshold
, toast.autovacuum_vacuum_insert_threshold
(integer
)
#Valeur spécifique à la table pour le paramètre autovacuum_vacuum_insert_threshold. La valeur spéciale de -1 peut être utilisée pour désactiver le vacuum lors des insertions sur la table.
autovacuum_vacuum_insert_scale_factor
, toast.autovacuum_vacuum_insert_scale_factor
(floating point
)
#Valeur spécifique à la table pour le paramètre autovacuum_vacuum_insert_scale_factor.
autovacuum_analyze_threshold
(integer
)
#Valeur spécifique à la table pour le paramètre autovacuum_analyze_threshold.
autovacuum_analyze_scale_factor
(floating point
)
#Valeur spécifique à la table pour le paramètre autovacuum_analyze_scale_factor.
autovacuum_vacuum_cost_delay
, toast.autovacuum_vacuum_cost_delay
(floating point
)
#Valeur spécifique à la table pour le paramètre autovacuum_vacuum_cost_delay.
autovacuum_vacuum_cost_limit
, toast.autovacuum_vacuum_cost_limit
(integer
)
#Valeur spécifique à la table pour le paramètre autovacuum_vacuum_cost_limit.
autovacuum_freeze_min_age
, toast.autovacuum_freeze_min_age
(integer
)
#
Valeur spécifique à la table pour le paramètre vacuum_freeze_min_age. Notez que l'autovacuum ignorera
les paramètres autovacuum_freeze_min_age
spécifiques
à la table qui sont plus importants que la moitié du paramètre autovacuum_freeze_max_age.
autovacuum_freeze_max_age
, toast.autovacuum_freeze_max_age
(integer
)
#
Valeur spécifique à la table pour le paramètre autovacuum_freeze_max_age. Notez que l'autovacuum ignorera
les paramètres autovacuum_freeze_max_age
spécifiques
à la table qui sont plus importants que la configuration globale (elle ne
peut être que plus petite).
autovacuum_freeze_table_age
, toast.autovacuum_freeze_table_age
(integer
)
#Valeur spécifique à la table pour le paramètre vacuum_freeze_table_age.
autovacuum_multixact_freeze_min_age
, toast.autovacuum_multixact_freeze_min_age
(integer
)
#
Valeur spécifique à la table pour le paramètre vacuum_multixact_freeze_min_age. Notez que l'autovacuum
ignorera les paramètres
autovacuum_multixact_freeze_min_age
spécifiques à la
table si leur configuration est supérieure à la moitié de la valeur du
paramètre global autovacuum_multixact_freeze_max_age.
autovacuum_multixact_freeze_max_age
, toast.autovacuum_multixact_freeze_max_age
(integer
)
#
Valeur spécifique à la table pour le paramètre autovacuum_multixact_freeze_max_age. Notez que
l'autovacuum ignorera les paramètres
autovacuum_multixact_freeze_max_age
spécifiques à la
table si leur configuration est supérieure à la valeur du paramètre
global (elle peut seulement être inférieure).
autovacuum_multixact_freeze_table_age
, toast.autovacuum_multixact_freeze_table_age
(integer
)
#Valeur spécifique à la table pour le paramètre vacuum_multixact_freeze_table_age.
log_autovacuum_min_duration
, toast.log_autovacuum_min_duration
(integer
)
#Valeur spécifique à la table pour le paramètre log_autovacuum_min_duration.
user_catalog_table
(boolean
)
#Déclare la table comme une autre table du catalogue dans le cadre de la réplication logique. Voir Section 49.6.2 pour les détails. Ce paramètre ne peut pas être configuré pour les tables TOAST.
PostgreSQL crée automatiquement un index pour chaque contrainte d'unicité ou clé primaire afin d'assurer l'unicité. Il n'est donc pas nécessaire de créer un index spécifiquement pour les colonnes de clés primaires. Voir CREATE INDEX pour plus d'informations.
Les contraintes d'unicité et les clés primaires ne sont pas héritées dans l'implantation actuelle. Cela diminue la fonctionnalité des combinaisons d'héritage et de contraintes d'unicité.
Une table ne peut pas avoir plus de 1600 colonnes (en pratique, la limite réelle est habituellement plus basse du fait de contraintes sur la longueur des lignes).
Créer une table films
et une table
distributeurs
:
CREATE TABLE films ( code char(5) CONSTRAINT premierecle PRIMARY KEY, titre varchar(40) NOT NULL, did integer NOT NULL, date_prod date, genre varchar(10), duree interval hour to minute ); CREATE TABLE distributeurs ( did integer PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, nom varchar(40) NOT NULL CHECK (nom <> '') );
Créer une table contenant un tableau à deux dimensions :
CREATE TABLE array_int ( vecteur int[][] );
Définir une contrainte d'unicité pour la table
films
. Les contraintes d'unicité de table peuvent être
définies sur une ou plusieurs colonnes de la table :
CREATE TABLE films ( code char(5), titre varchar(40), did integer, date_prod date, genre varchar(10), duree interval hour to minute, CONSTRAINT production UNIQUE(date_prod) );
Définir une contrainte de vérification sur une colonne :
CREATE TABLE distributeurs ( did integer CHECK (did > 100), nom varchar(40) );
Définir une contrainte de vérification sur la table :
CREATE TABLE distributeurs ( did integer, nom varchar(40), CONSTRAINT con1 CHECK (did > 100 AND nom <> '') );
Définir une contrainte de clé primaire sur la table films
.
CREATE TABLE films ( code char(5), titre varchar(40), did integer, date_prod date, genre varchar(10), duree interval hour to minute, CONSTRAINT code_titre PRIMARY KEY(code,titre) );
Définir une contrainte de clé primaire pour la table
distributeurs
. Les deux exemples suivants sont équivalents,
le premier utilise la syntaxe de contrainte de table, le second la
syntaxe de contrainte de colonne :
CREATE TABLE distributeurs ( did integer, nom varchar(40), PRIMARY KEY(did) ); CREATE TABLE distributeurs ( did integer PRIMARY KEY, nom varchar(40) );
Affecter une valeur par défaut à la colonne nom
,
une valeur par défaut à la colonne did
,
engendrée à l'aide d'une séquence, et une valeur par défaut à
la colonne modtime
, équivalente au moment où la ligne
est insérée :
CREATE TABLE distributeurs ( name varchar(40) DEFAULT 'Luso Films', did integer DEFAULT nextval('distributeurs_serial'), modtime timestamp DEFAULT current_timestamp );
Définir deux contraintes de colonnes NOT NULL
sur la table
distributeurs
, dont l'une est explicitement nommée :
CREATE TABLE distributeurs ( did integer CONSTRAINT no_null NOT NULL, nom varchar(40) NOT NULL );
Définir une contrainte d'unicité sur la colonne nom
:
CREATE TABLE distributeurs ( did integer, nom varchar(40) UNIQUE );
La même chose en utilisant une contrainte de table :
CREATE TABLE distributeurs ( did integer, nom varchar(40), UNIQUE(nom) );
Créer la même table en spécifiant un facteur de remplissage de 70% pour la table et les index uniques :
CREATE TABLE distributeurs ( did integer, nom varchar(40), UNIQUE(nom) WITH (fillfactor=70) ) WITH (fillfactor=70);
Créer une table cercles
avec une contrainte
d'exclusion qui empêche le croisement de deux cercles :
CREATE TABLE cercles ( c circle, EXCLUDE USING gist (c WITH &&) );
Créer une table cinemas
dans le tablespace
diskvol1
:
CREATE TABLE cinemas ( id serial, nom text, emplacement text ) TABLESPACE diskvol1;
Créer un type composite et une table typée :
CREATE TYPE type_employe AS (nom text, salaire numeric); CREATE TABLE employes OF type_employe ( PRIMARY KEY (nom), salaire WITH OPTIONS DEFAULT 1000 );
Créer une table partitionée par intervalles :
CREATE TABLE measurement ( logdate date not null, peaktemp int, unitsales int ) PARTITION BY RANGE (logdate);
Créer une table partitionnée par intervalles avec plusieurs colonnes dans la clé de partitionnement :
CREATE TABLE measurement_year_month ( logdate date not null, peaktemp int, unitsales int ) PARTITION BY RANGE (EXTRACT(YEAR FROM logdate), EXTRACT(MONTH FROM logdate));
Créer une table partitionnée par liste de valeurs :
CREATE TABLE cities ( city_id bigserial not null, name text not null, population bigint ) PARTITION BY LIST (left(lower(name), 1));
Créer une table partitionnée par hachage :
CREATE TABLE orders ( order_id bigint not null, cust_id bigint not null, status text ) PARTITION BY HASH (order_id);
Créer une partition d'une table partitionnée par intervalles :
CREATE TABLE measurement_y2016m07 PARTITION OF measurement ( unitsales DEFAULT 0 ) FOR VALUES FROM ('2016-07-01') TO ('2016-08-01');
Créer quelques partitions d'une table partitionnée par intervalles avec plusieurs colonnes dans la clé de partitionnement :
CREATE TABLE measurement_ym_older PARTITION OF measurement_year_month FOR VALUES FROM (MINVALUE, MINVALUE) TO (2016, 11); CREATE TABLE measurement_ym_y2016m11 PARTITION OF measurement_year_month FOR VALUES FROM (2016, 11) TO (2016, 12); CREATE TABLE measurement_ym_y2016m12 PARTITION OF measurement_year_month FOR VALUES FROM (2016, 12) TO (2017, 01); CREATE TABLE measurement_ym_y2017m01 PARTITION OF measurement_year_month FOR VALUES FROM (2017, 01) TO (2017, 02);
Créer une partition d'une table partitionnée par liste de valeur :
CREATE TABLE cities_ab PARTITION OF cities ( CONSTRAINT city_id_nonzero CHECK (city_id != 0) ) FOR VALUES IN ('a', 'b');
Créer une partition d'une table partitionnée par liste e valeur qui est elle-même partitionnée, puis y ajouter une partition :
CREATE TABLE cities_ab PARTITION OF cities ( CONSTRAINT city_id_nonzero CHECK (city_id != 0) ) FOR VALUES IN ('a', 'b') PARTITION BY RANGE (population); CREATE TABLE cities_ab_10000_to_100000 PARTITION OF cities_ab FOR VALUES FROM (10000) TO (100000);
Créer des partitions d'une table partitionnée par hachage :
CREATE TABLE orders_p1 PARTITION OF orders FOR VALUES WITH (MODULUS 4, REMAINDER 0); CREATE TABLE orders_p2 PARTITION OF orders FOR VALUES WITH (MODULUS 4, REMAINDER 1); CREATE TABLE orders_p3 PARTITION OF orders FOR VALUES WITH (MODULUS 4, REMAINDER 2); CREATE TABLE orders_p4 PARTITION OF orders FOR VALUES WITH (MODULUS 4, REMAINDER 3);
Créer une partition par défaut :
CREATE TABLE cities_partdef PARTITION OF cities DEFAULT;
La commande CREATE TABLE
est conforme au standard
SQL, aux exceptions indiquées ci-dessous.
Bien que la syntaxe de CREATE TEMPORARY TABLE
ressemble
à celle du SQL standard, l'effet n'est pas le même. Dans le standard, les
tables temporaires sont définies une seule fois et existent
automatiquement (vide de tout contenu au démarrage) dans toute session les
utilisant. PostgreSQL, au contraire, impose
à chaque session de lancer une commande CREATE TEMPORARY
TABLE
pour chaque table temporaire utilisée. Cela permet à
des sessions différentes d'utiliser le même nom de table temporaire dans des
buts différents (le standard contraint toutes les
instances d'une table temporaire donnée à pointer sur la même structure de
table).
Le comportement des tables temporaires tel que défini par le standard est largement ignorée. Le comportement de PostgreSQL sur ce point est similaire à celui de nombreuses autres bases de données SQL.
Le standard SQL distingue aussi les tables temporaires globales et locales. Une table temporaire local a un contenu séparé pour chaque module SQL à l'intérieur de chaque session bien que sa définition est toujours partagée entre les sessions. Comme PostgreSQL ne supporte pas les modules SQL, la distinction n'a pas de raison d'être avec PostgreSQL.
Pour des raisons
de compatibilité, PostgreSQL accepte
néanmoins les mots-clés GLOBAL
et LOCAL
dans la définition d'une table temporaire, mais ils n'ont actuellement aucun effet.
L'utilisation de ces mots clés n'est pas conseillée car les versions futures
de PostgreSQL pourrait adopter une interprétation
plus standard de leur signification.
La clause ON COMMIT
sur les tables temporaires
diffère quelque peu du standard SQL. Si la clause
ON COMMIT
est omise, SQL spécifie ON COMMIT DELETE ROWS
comme comportement par défaut. PostgreSQL utilise
ON COMMIT PRESERVE ROWS
par défaut. De plus, l'option
ON COMMIT DROP
n'existe pas en SQL.
Quand une contrainte UNIQUE
ou PRIMARY
KEY
est non déferrable, PostgreSQL
vérifie l'unicité immédiatement après qu'une ligne soit insérée ou
modifiée. Le standard SQL indique que l'unicité doit être forcée seulement
à la fin de l'instruction ; ceci fait une différence quand, par
exemple, une seule commande met à jour plusieurs valeurs de clés. Pour
obtenir un comportement compatible au standard, déclarez la contrainte
comme DEFERRABLE
mais non déferrée (c'est-à-dire que
INITIALLY IMMEDIATE
). Faites attention que cela peut
être beaucoup plus lent qu'une vérification d'unicité immédiate.
Dans le standard, les contraintes de vérification CHECK
de colonne ne peuvent faire référence qu'à la colonne à laquelle elles
s'appliquent ; seules les contraintes CHECK
de table
peuvent faire référence à plusieurs colonnes.
PostgreSQL n'impose pas cette restriction ;
les contraintes de vérifications de colonnes et de table ont un traitement
identique.
EXCLUDE
Constraint
Le type de contrainte EXCLUDE
est une extension
PostgreSQL.
La possibilité d'indiquer des listes de colonne dans les actions
SET DEFAULT
et SET NULL
des clés
étrangères est une extension PostgreSQL.
PostgreSQL étend la contrainte de clé étrangère en permettant de référencer les colonnes d'un index d'unicité au lieu des colonnes d'une clé primaire ou d'une contrainte d'unicité.
NULL
La « contrainte » NULL
(en fait, une
non-contrainte) est une extension PostgreSQL au
standard SQL, incluse pour des raisons de compatibilité avec d'autres
systèmes de bases de données (et par symétrie avec la contrainte
NOT NULL
). Comme c'est la valeur par défaut de toute
colonne, sa présence est un simple bruit.
Le standard SQL stipule que les contraintes de table et de domaine doivent
avoir des noms uniques sur le schéma contenant la table ou le domaine.
PostgreSQL est laxiste : il requiert
seulement que le nom des contraintes soit unique parmi les contraintes
attachées à une table ou un domaine particulier. Néanmoins, cette liberté
supplémentaire n'existe pas pour les contraintes basées sur des index
(contraintes UNIQUE
, PRIMARY KEY
et
EXCLUDE
) parce que l'index associé est nommé de la même
façon que la contrainte, et les noms d'index doivent être uniques parmi
toutes les relations du même schéma.
Actuellement, PostgreSQL n'enregistre pas de
noms pour les contraintes NOT NULL
, donc elles ne sont
pas sujettes aux restrictions d'unicité. Ceci pourrait changer dans une
prochaine version.
L'héritage multiple via la clause INHERITS
est une
extension du langage PostgreSQL. SQL:1999 et
les versions ultérieures définissent un héritage simple en utilisant une
syntaxe et des sémantiques différentes. L'héritage style SQL:1999 n'est pas
encore supporté par PostgreSQL.
PostgreSQL autorise la création de tables sans
colonne (par exemple, CREATE TABLE foo();
). C'est une
extension du standard SQL, qui ne le permet pas. Les tables sans colonne ne
sont pas très utiles mais les interdire conduit à un comportement étrange de
ALTER TABLE DROP COLUMN
. Il est donc plus sage
d'ignorer simplement cette restriction.
PostgreSQL autorise une table à avoir plus d'une
colonne d'identité. le standard spécifie qu'une table peut avoir au plus
une colonne d'identité. Cette règle est assouplie principalement pour
donner plus de flexibilité pour effectuer des changements de schéma ou des
migrations. Veuillez noter que la commande INSERT
supporte uniquement une seule clause de surcharge qui s'appliquent à la
commande entière, et donc avoir de multiples colonnes d'identités avec des
comportements différents n'est pas bien supporté.
L'option STORED
n'est pas standard mais est également
utilisée dans d'autres implémentations SQL. le standard SQL ne spécifie pas
de clause de stockage pour les colonnes générées.
LIKE
Alors qu'une clause LIKE
existe dans le standard SQL,
beaucoup des options acceptées par PostgreSQL
ne sont pas dans le standard, et certaines options du standard ne sont pas
implémentées dans PostgreSQL.
WITH
La clause WITH
est une extension
PostgreSQL ; les paramètres de stockage
ne sont pas dans le standard.
Le concept PostgreSQL de tablespace n'est
pas celui du standard. De ce fait, les clauses TABLESPACE
et USING INDEX TABLESPACE
sont des extensions.
Les tables typées implémentent un sous-ensemble du standard SQL. Suivant le standard, une table typée a des colonnes correspondant au type composite ainsi qu'une autre colonne qui est la « colonne auto-référente ». PostgreSQL ne supporte pas ces colonnes auto-référentes explicitement.
PARTITION BY
La clausePARTITION BY
est une extension
PostgreSQL de la norme SQL.
PARTITION OF
La clause PARTITION OF
est une extension
PostgreSQL de la norme SQL.