- Club d'entraide des développeurs francophones

Documentation PostgreSQL 8.2.5 -

Commandes SQL

Précédent
CREATE SEQUENCE
Suivant
CREATE TABLE AS
Niveau supérieur.
Sommaire

CREATE TABLE

CREATE TABLE — Définir une nouvelle table

Synopsis

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 | CONSTRAINTS } ] }
    [, ... ]
] )
[ INHERITS ( table_parent [, ... ] ) ]
[ WITH ( parametre_stockage [= valeur] [, ... ] ) | WITH OIDS | WITHOUT OIDS ]
[ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]
[ TABLESPACE tablespace ]

où contrainte_colonne
peut être :

[ CONSTRAINT nom_contrainte ]
{ NOT NULL | NULL |
  UNIQUE parametres_index |
  PRIMARY KEY parametres_index |
  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 [, ... ] ) parametres_index |
  PRIMARY KEY ( nom_colonne [, ... ] ) parametres_index |
  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 ]

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

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

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

Paramètres

TEMPORARY ou TEMP

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 ou PRIMARY KEY. Les paramètres de stockage des index sont documentés dans CREATE INDEX. Le seul paramètre de stockage actuellement disponible pour les tables est :

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

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

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 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 cinemas dans le tablespace diskvol1 :

CREATE TABLE cinemas (
    id serial,
    nom text,
    emplacement text
) TABLESPACE diskvol1;

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.

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.

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.

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.

`WITH` clause

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.

Voir aussi

ALTER TABLE, DROP TABLE, CREATE TABLESPACE

CREATE SEQUENCE

CREATE TABLE AS Suivant