CREATE SEQUENCE
CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } ] TABLE nom_table ( [
{ nom_colonne type_donnees [ DEFAULT default_expr ] [ contrainte_colonne [ ... ] ]
| contrainte_table
| LIKE table_parent [ { INCLUDING | EXCLUDING } DEFAULTS ] }
[, ... ]
] )
[ INHERITS ( table_parent [, ... ] ) ]
[ WITH OIDS | WITHOUT OIDS ]
[ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]
[ TABLESPACE espace_logique ]
où contrainte_colonne
peut être :
[ CONSTRAINT nom_contrainte ]
{ NOT NULL | NULL | UNIQUE [ USING INDEX TABLESPACE espacelogique ] |
PRIMARY KEY [ USING INDEX TABLESPACE espacelogique ] |
CHECK (expression) |
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 contrainte_table :
[ CONSTRAINT nom_contrainte ]
{ UNIQUE ( nom_colonne [, ... ] ) [ USING INDEX TABLESPACE espacelogique ] |
PRIMARY KEY ( nom_colonne [, ...
] ) [ USING INDEX TABLESPACE espacelogique ] |
CHECK ( expression ) |
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 ]
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 ou vues 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 spécifient 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.
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.
On peut éventuellement écrire GLOBAL ou LOCAL avant TEMPORARY ou TEMP. Cela ne fait pas de différence dans PostgreSQL™ (cf. Compatibilité).
Le nom (éventuellement qualifié du nom du schéma) de la table à créer.
Le nom d'une colonne de la nouvelle table.
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.
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.
La clause optionnelle INHERITS spécifie 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. Néanmoins, les déclarations des colonnes héritées et des nouvelles de même nom n'ont pas l'obligation de spécifier des contraintes identiques : toutes les contraintes sont assemblées et appliquées à la nouvelle table. 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.
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 toutes les colonnes de la nouvelle table.
Cette clause optionnelle précise si les lignes de la nouvelle table se voient affecter des OID (identifiants d'objets). Si ni WITH OIDS ni WITHOUT OIDS ne sont spécifiés, la valeur par défaut dépend du paramètre de configuration default_with_oids. Si la nouvelle table hérite de tables possédant des OID, alors WITH OIDS est imposé même si la commande indique WITHOUT OIDS.
Si WITHOUT OIDS est spécifié (même implicitemnt), la nouvelle table ne stocke pas les OID et aucun OID ne sera affecté à une ligne qui y est insérée. C'est généralement intéressant pour les grosses tables car cela réduit la consommation d'OID. De ce fait, il n'y a pas, pour cette table de risque de collision (NDT : wraparound, en VO) du compteur d'OID, compteur sur 32 bits. Lorsque le compteur revient à zéro, l'unicité des OID ne peut plus être garantie, ce qui en réduit considérablement l'utilité. De plus, exclure les OID d'une table réduit l'espace requis pour stocker la table sur disque de quatre octets par ligne de table (sur la plupart des machines), améliorant ainsi leur performance.
Pour supprimer les OID d'une table après sa création, on utilise ALTER TABLE.
Le nom optionnel d'une contrainte de colonne ou de table. S'il n'est pas spécifié, le système engendre un nom.
Interdiction des valeurs NULL dans la colonne.
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.
La contrainte UNIQUE spécifie qu'un groupe de colonnes d'une table ne peut contenir que des valeurs uniques. Le comportement de la contrainte de table est le même que celui des contraintes de colonnes avec la possibilité supplémentaire de grouper des colonnes.
Les valeurs NULL ne sont pas considérées égales dans le cas d'une contrainte d'unicité.
Toute contrainte d'unicité sur la table doit nommer un ensemble de colonnes différent de celui utilisé par quelque autre contrainte d'unicité ou de clé primaire sur la table. Dans le cas contraire, cela revient à définir deux fois la même contrainte.
Une contrainte de clé primaire est utilisée pour spécifier des colonnes qui ne peuvent contenir que des valeurs uniques, non NULL. Techniquement, PRIMARY KEY n'est qu'une combinaison de UNIQUE et NOT NULL. Toutefois, identifier un ensemble de colonnes comme clé primaire fournit des informations sur les schémas. En effet, une clé primaire implique que d'autres tables puissent utiliser cet ensemble de colonnes comme identifiant unique de ligne.
Une seule clé primaire peut être spécifiée par table, qu'il s'agisse d'une contrainte de colonne ou de table.
La contrainte de clé primaire doit nommer un ensemble de colonnes différent de tout autre ensemble de colonnes utilisé par une contrainte d'unicité sur la table.
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.
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 colonne_reference est omis, 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 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. MATCH SIMPLE autorise une colonne de clé étrangère à être NULL même si les autres parties de la clé étrangère ne sont pas nulles. MATCH PARTIAL n'est pas encore implanté.
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 :
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.
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.
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.
La valeur de la colonne qui référence est positionnée à NULL.
La valeur de la colonne qui référence est positionnée à celle par défaut.
Si les colonnes référencées sont modifiées fréquemment, il est conseillé d'ajouter un index sur la colonne de clé étrangère de façon à accélérer les actions référentielles associées à la colonne de clé étrangère.
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. Seules les contraintes de clé étrangère acceptent cette clause. Les autres types de contraintes ne peuvent être différées.
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.
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 :
Aucune action n'est entreprise à la fin des transactions. Comportement par défaut.
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.
La table temporaire est supprimée à la fin du bloc de transactions.
espacelogique est le nom du tablespace dans lequel est créée la nouvelle table. S'il n'est pas spécifié, default_tablespace est utilisé (ou le tablespace par défaut de la base de données si default_tablespace est une chaîne vide).
Les index associés à une contrainte UNIQUE ou PRIMARY KEY sont créés dans le tablespace nommé espacelogique. S'il n'est pas spécifié, default_tablespace est utilisé (ou le tablespace par défaut de la base de données si default_tablespace est une chaîne vide).
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]](images/tip.png)
L'utilisation de WITHOUT OIDS 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 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 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 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. Les contraintes de clé primaire 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 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
);
L'exemple précédant est équivalent au suivant, qui utilise une contrainte de table :
CREATE TABLE distributeurs (
did integer,
nom varchar(40),
UNIQUE(nom)
);
Créer une table cinemas dans le tablespace diskvol1 :
CREATE TABLE cinemas (
id serial,
nom text,
emplacement text
) TABLESPACE diskvol1;
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.
PostgreSQL™ ne respecte pas la distinction imposée par le standard entre tables temporaires globales et locales. En effet, cette distinction repose sur le concept de modules que PostgreSQL™ ne gère pas. 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 aucun effet.
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.
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.
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.
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.