PostgreSQLLa base de données la plus sophistiquée au monde.

Version anglaise

CREATE TABLE

CREATE TABLE — Définir une nouvelle table

Synopsis

              CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXISTS ] nom_table ( [
  { nom_colonne type_donnees [ COLLATE collation ] [ contrainte_colonne [ ... ] ]
    | contrainte_table
    | LIKE table_source [ option_like ... ] }
    [, ... ]
] )
[ INHERITS ( table_parent [, ... ] ) ]
[ WITH ( parametre_stockage [= valeur] [, ... ] ) | WITH OIDS | WITHOUT OIDS ]
[ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]
[ TABLESPACE nom_tablespace ]

CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE nom_table
    OF nom_type [ (
  { nom_colonne WITH OPTIONS [ contrainte_colonne [ ... ] ]
    | contrainte_table }
    [, ... ]
) ]
[ WITH ( parametre_stockage [= valeur] [, ... ] ) | WITH OIDS | WITHOUT OIDS ]
[ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]
[ TABLESPACE nom_tablespace ]

contrainte_colonne
peut être :

[ CONSTRAINT nom_contrainte ]
{ NOT NULL | NULL |
  CHECK ( expression ) [ NO INHERIT ] |
  DEFAULT expression_par_défaut |
  UNIQUE parametres_index |
  PRIMARY KEY parametres_index |
  EXCLUDE [ USING methode_index ] ( élément_exclude WITH opérateur [, ... ] ) paramètres_index [ WHERE ( prédicat ) ] |
  REFERENCES table_reference [ ( colonne_reference ) ] [ MATCH FULL
| MATCH PARTIAL | MATCH SIMPLE ]
    [ ON DELETE action ] [ ON UPDATE action ] }
[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]

et option_like peut
valoir :

{ INCLUDING | EXCLUDING } { DEFAULTS | CONSTRAINTS | INDEXES | STORAGE | COMMENTS | ALL }

et contrainte_table :

[ CONSTRAINT nom_contrainte ]
{ UNIQUE ( nom_colonne [, ... ] ) parametres_index |
  PRIMARY KEY ( nom_colonne [, ... ] ) parametres_index |
  CHECK ( expression ) [ NO INHERIT ] |
  FOREIGN KEY ( nom_colonne [, ...
] ) REFERENCES table_reference [ (
colonne_reference [, ... ] ) ]
    [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ] [ ON DELETE action ] [ ON UPDATE action ] }
[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]

Les paramètres_index dans les
contraintes UNIQUE, PRIMARY KEY et
EXCLUDE sont :

[ WITH ( paramètre_stockage [= valeur] [, ... ] ) ]
[ USING INDEX TABLESPACE nom_tablespace ]

exclude_element dans une
contrainte EXCLUDE peut valoir :

{ nom_colonne | ( expression ) } [ opclass ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ]

Description

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 tables, séquences, index, vues ou tables distantes 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.

Paramètres

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). Les tables permanentes qui portent le même nom ne sont pas visibles dans la session courante 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 la section intitulée « 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 29, Fiabilité et journaux de transaction), 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é.

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, Types de données.

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é.

INHERITS ( table_parent [, ... ])

La clause optionnelle INHERITS indique une liste de tables dont les colonnes sont automatiquement héritées par la nouvelle table.

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.

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.

Les expressions par défaut des définitions de colonnes ne seront copiées que si INCLUDING DEFAULTS est spécifié. Le comportement par défaut les exclut, ce qui conduit à des valeurs par défaut NULL pour les colonnes copiées de la nouvelle table. Notez que copier les valeurs par défaut appelant des fonctions de modification de la base de données, comme nextval, pourraient créer un lien fonctionnel entre les tables originale et nouvelle.

Les contraintes NOT NULL sont toujours copiées sur la nouvelle table. Les contraintes CHECK sont copiées seulement si la clause INCLUDING CONSTRAINTS est précisée. Aucune distinction n'est faite entre les contraintes au niveau colonne et les contraintes au niveau table.

Les index, les contraintes PRIMARY KEY, UNIQUE et EXCLUDE sur la table originale seront créés sur la nouvelle table seulement si la clause INCLUDING INDEXES est spécifiée. Les noms des nouveaux index et des nouvelles contraintes sont choisis suivant les règles par défaut, quelque soit la façon dont les originaux étaient appelés. (Ce comportement évite les potentiels échecs de nom dupliqué pour les nouveaux index.)

Des paramètres STORAGE pour les définitions de la colonne copiée seront seulement copiés si INCLUDING STORAGE est spécifié. Le comportement par défaut est d'exclure des paramètres STORAGE, résultant dans les colonnes copiées dans la nouvelle table ayant des paramètres par défaut spécifiques par type. Pour plus d'informations sur STORAGE, voir Section 58.2, « TOAST ».

Les commentaires pour les colonnes, contraintes et index copiés seront seulement copiés si INCLUDING COMMENTS est spécifié. Le comportement par défaut est d'exclure les commentaires, ce qui résulte dans des colonnes et contraintes copiées dans la nouvelle table mais sans commentaire.

INCLUDING ALL est une forme abrégée de INCLUDING DEFAULTS INCLUDING CONSTRAINTS INCLUDING INDEXES INCLUDING STORAGE INCLUDING COMMENTS.

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 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.

Une contrainte marquée NO INHERIT ne sera pas propagée aux tables filles.

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 (les sous-requêtes et références croisées aux autres colonnes de la table courante ne sont pas autorisées). 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.

UNIQUE (contrainte de colonne), UNIQUE ( 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 de table unique est le même que celui des contraintes de colonnes avec la capacité supplémentaire de traiter plusieurs colonnes.

Pour une contrainte unique, les valeurs NULL ne sont pas considérées comme égales.

Chaque contrainte unique de table doit nommer un ensemble de colonnes qui est différent de l'ensemble de colonnes nommées par toute autre contrainte unique ou de clé primaire définie pour la table. (Sinon elle ne ferait que lister la même contrainte deux fois.)

PRIMARY KEY (contrainte de colonne), PRIMARY KEY ( 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 mais 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.

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, « Types géométriques ») en utilisant l'opérateur &&.

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.9, « Classes et familles d'opérateurs ») pour la méthode d'accès par index, nommée méthode_index. Les opérateurs doivent être commutatifs. Chaque élément_exclusion peut spécifier en option une classe d'opérateur et/ou des options de tri ; ils sont décrits complètement sous CREATE INDEX(7).

La méthode d'accès doit supporter amgettuple (voir Chapitre 54, Définition de l'interface des méthodes d'accès aux index) ; 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 matchtype ] [ ON DELETE action ] [ ON UPDATE action ] (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. Les colonnes référencées doivent être celles d'une contrainte d'unicité ou de clé primaire, non déferrable, dans 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

La valeur de la colonne qui référence est positionnée à NULL.

SET DEFAULT

(Il doit existe 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(7)). 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 contraintesNOT NULL et CHECK ne sont pas déferrables.

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(7).

WITH ( paramètre_stockage [= valeur] [, ... ] )

Cette clause spécifie les paramètres de stockage optionnels pour une table ou un index ; voir la section intitulée « Paramètres de stockage » pour plus d'informations. La clause WITH peut aussi inclure pour une table OIDS=TRUE (ou simplement OIDS) pour indiquer que les lignes de la nouvelle table doivent se voir affecter des OID (identifiants d'objets) ou OIDS=FALSE pour indiquer que les lignes ne doivent pas avoir d'OID. Si OIDS n'est pas indiqué, la valeur par défaut dépend du paramètre de configuration default_with_oids. (Si la nouvelle table hérite d'une table qui a des OID, alorsOIDS=TRUE est forcé même si la commande précise OIDS=FALSE.)

Si OIDS=FALSE est indiqué ou implicite, la nouvelle table ne stocke pas les OID et aucun OID n'est affecté pour une ligne insérée dans cette table. Ceci est généralement bien considéré car cela réduit la consommation des OID et retarde du coup le retour à zéro du compteur sur 32 bits. Une fois que le compteur est revenu à zéro, les OID ne sont plus considérés uniques ce qui les rend beaucoup moins utiles. De plus, exclure les OID d'une table réduit l'espace requis pour stocker la table sur le disque de quatre octets par ligne (la plupart des machines), améliorant légèrement les performances.

Pour supprimer les OID d'une table une fois qu'elle est créée, utilisez ALTER TABLE(7).

WITH OIDS, WITHOUT OIDS

Ce sont les syntaxes obsolètes mais équivalentes, respectivement de WITH (OIDS) et WITH (OIDS=FALSE). Si vous souhaitez indiquer à la fois l'option OIDS et les paramètres de stockage, vous devez utiliser la syntaxe WITH ( ... ) ; voir ci-dessus.

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(7) automatique est réalisé à chaque validation.

DROP

La table temporaire est supprimée à la fin du bloc de transactions.

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é.

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é.

Paramètres de stockage

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(7). Les paramètres de stockage actuellement disponibles pour les tables sont listés ci-dessous. Pour chaque paramètre, sauf contre-indication, il y a un paramètre additionnel, de même nom mais préfixé par toast., qui peut être utilisé pour contrôler le le comportement de la table TOAST (stockage supplémentaire), si elle existe (voir Section 58.2, « TOAST » pour plus d'informations sur TOAST). La table TOAST hérite ses valeurs autovacuum de sa table parente s'il n'y a pas de paramètre toast.autovacuum_* positionné.

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. 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.

autovacuum_enabled, toast.autovacuum_enabled (boolean)

Active ou désactive le processus d'autovacuum sur une table particulière. Si à true, le processus d'autovacuum démarrera une opération VACUUM sur une table particulière quand le nombre d'enregistrements mis à jour ou supprimés dépassera autovacuum_vacuum_threshold plus autovacuum_vacuum_scale_factor multiplié par le nombre d'enregistrements estimés actifs dans la relation. De façon similaire, il démarrera une opération ANALYZE quand le nombre d'enregistrements insérés, mis à jour ou supprimés dépassera autovacuum_analyze_threshold plus autovacuum_analyze_scale_factor multiplié par le nombre d'enregistrements estimés actifs dans la relation. Si à false, la table ne sera pas traitée par autovacuum, sauf pour prévenir le bouclage des identifiants de transaction. Voir Section 23.1.5, « Éviter les cycles des identifiants de transactions » pour plus d'information sur la prévention de ce bouclage. Notez que cette variable hérite sa valeur du paramètre autovacuum.

autovacuum_vacuum_threshold, toast.autovacuum_vacuum_threshold (integer)

Nombre minimum d'enregistrements mis à jour ou supprimés avant de démarrer une opération VACUUM sur une table particulière.

autovacuum_vacuum_scale_factor, toast.autovacuum_vacuum_scale_factor (float4)

Coefficient multiplicateur pour reltuples (nombre estimé d'enregistrements d'une relation) à ajouter à autovacuum_vacuum_threshold.

autovacuum_analyze_threshold (integer)

Nombre minimum d'enregistrements insérés, mis à jour ou supprimés avant de démarrer une opération ANALYZE sur une table particulière.

autovacuum_analyze_scale_factor (float4)

Coefficient multiplicateur pour reltuples (nombre estimé d'enregistrements d'une relation) à ajouter à autovacuum_analyze_threshold.

autovacuum_vacuum_cost_delay, toast.autovacuum_vacuum_cost_delay (integer)

Paramètre autovacuum_vacuum_cost_delay personnalisé.

autovacuum_vacuum_cost_limit, toast.autovacuum_vacuum_cost_limit (integer)

Paramètre autovacuum_vacuum_cost_limit personnalisé.

autovacuum_freeze_min_age, toast.autovacuum_freeze_min_age (integer)

Paramètre vacuum_freeze_min_age personnalisé. Notez que autovacuum rejettera les tentatives de positionner un autovacuum_freeze_min_age plus grand que le paramètre autovacuum_freeze_max_age à la moitié de la plage système d'identifiants.

autovacuum_freeze_max_age, toast.autovacuum_freeze_max_age (integer)

Paramètre autovacuum_freeze_max_age personnalisé. L'autovacuum rejettera les tentatives de positionner un autovacuum_freeze_max_age plus grand que le paramètre système (il ne peut être que plus petit).

autovacuum_freeze_table_age, toast.autovacuum_freeze_table_age (integer)

Paramètre vacuum_freeze_table_age personnalisé.

autovacuum_multixact_freeze_min_age, toast.autovacuum_multixact_freeze_min_age (integer)

Paramètre vacuum_multixact_freeze_min_age personnalisé. Il faut noter que l'autovacuum ignorera les tentatives de configuration par table d'un autovacuum_multixact_freeze_min_age plus grand que la moitié du paramètre autovacuum_multixact_freeze_max_age au niveau système.

autovacuum_multixact_freeze_max_age, toast.autovacuum_multixact_freeze_max_age (integer)

Paramètre autovacuum_multixact_freeze_max_age personnalisé. Il faut noter que l'autovacuum ignorera les tentatives de configuration par table d'un autovacuum_multixact_freeze_max_age plus grand que la valeur du paramètre autovacuum_multixact_freeze_max_age au niveau système (il peut seulement être configuré plus petit). Notez que, si vous pouvez configurer autovacuum_multixact_freeze_max_age très bas, voire même à zéro, c'est généralement déconseillé vu que cela force des VACUUM fréquents.

autovacuum_multixact_freeze_table_age, toast.autovacuum_multixact_freeze_table_age (integer)

Paramètre vacuum_multixact_freeze_table_age personnalisé.

Notes

Utiliser les OID dans les nouvelles applications n'est pas recommandé : dans la mesure du possible, un type SERIAL ou un autre générateur de séquence sera utilisé comme clé primaire de la table. Néanmoins, si l'application utilise les OID pour identifier des lignes spécifiques d'une table, il est recommandé de créer une contrainte unique sur la colonne oid de cette table afin de s'assurer que les OID de la table identifient les lignes de façon réellement unique même si le compteur est réinitialisé. Il n'est pas garanti que les OID soient uniques sur l'ensemble des tables. Dans le cas où un identifiant unique sur l'ensemble de la base de données est nécessaire, on utilise préférentiellement une combinaison de tableoid et de l'OID de la ligne.

[Astuce]

Astuce

L'utilisation de OIDS=FALSE est déconseillée pour les tables dépourvues de clé primaire. En effet, sans OID ou clé de données unique, il est difficile d'identifier des lignes spécifiques.

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écifiqueme pour les colonnes de clés primaires. Voir CREATE INDEX(7) 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).

Exemples

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 DEFAULT nextval('serial'),
     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
);

Compatibilité

La commande CREATE TABLE est conforme au standard SQL, aux exceptions indiquées ci-dessous.

Tables temporaires

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 comportemant par défaut. PostgreSQL™ utilise ON COMMIT PRESERVE ROWS par défaut. De plus, l'option ON COMMIT DROP n'existe pas en SQL.

Contraintes d'unicité non déferrées

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.

Contraintes de vérification de colonnes

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™.

Contrainte 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.

Héritage

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™.

Tables sans colonne

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.

Clause 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™.

Clause WITH

La clause WITH est une extension PostgreSQL™ ; ni les paramètres de stockage ni les OID ne sont dans le standard.

Tablespaces

Le concept PostgreSQL™ de tablespace n'est pas celui du standard. De ce fait, les clauses TABLESPACE et USING INDEX TABLESPACE sont des extensions.

Tables typées

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 mais le même effet est disponible en utilisant la fonctionnalité OID.