CREATE TABLE AS — Définir une nouvelle table à partir des résultats d'une requête
CREATE TABLE
CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE nom_table
[ (nom_colonne [, ...] ) ]
[ WITH ( parametre_stockage [= valeur] [, ... ] ) | WITH OIDS | WITHOUT OIDS ]
[ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]
[ TABLESPACE espace_logique ]
AS requête
[ WITH [ NO ] DATA ]
CREATE TABLE AS crée une table et y insère les données récupérées par une commande SELECT. Les colonnes de la table ont les noms et les types de données associés aux colonnes en sortie du SELECT (les noms des colonnes peuvent toutefois être surchargés).
CREATE TABLE AS semble possèder des similitudes avec la création d'une vue mais est, en fait, assez différente : elle crée une nouvelle table et n'évalue la requête qu'une seule fois, pour le chargement initial de la nouvelle table. Les modifications ultérieures de la table source ne sont pas prises en compte. Au contraire, une vue réévalue l'instruction SELECT de définition à chaque appel.
Ignoré. Conservé pour la compatibilité (cf. CREATE TABLE(7)).
Si spécifié, la table est temporaire (cf. CREATE TABLE(7)).
Si spécifié, la table est créée comme une table non tracée dans les journaux de transactions. Voir CREATE TABLE(7) pour plus de détails.
Le nom de la table à créer (éventuellement qualifié du nom du schéma).
Le nom d'une colonne dans la nouvelle table. Si les noms de colonnes ne sont pas précisés, ils sont issus des noms des colonnes en sortie de la requête. Les noms des colonnes ne peuvent pas être précisés lorsque la table est créée à partir d'une commande EXECUTE.
Cette clause indique les paramètres de stockage optionnels pour la nouvelle table ; voir la section intitulée « Paramètres de stockage » pour plus d'informations. La clause WITH peut aussi inclure OIDS=TRUE (ou simplement OIDS) pour indiquer que les lignes de la nouvelle table doivent avoir des OID (identifiants d'objets) ou OIDS=FALSE pour indiquer le contraire. Voir CREATE TABLE(7) pour plus d'informations.
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.
Le comportement des tables temporaires à la fin d'un bloc de transaction est contrôlable en utilisant ON COMMIT. Voici les trois options :
Aucune action spéciale n'est effectuée à la fin de la transaction. C'est le comportement par défaut.
Toutes les lignes de la table temporaire seront supprimées à la fin de chaque bloc de transaction. Habituellement, un TRUNCATE(7) automatique est effectué à chaque COMMIT.
La table temporaire sera supprimée à la fin du bloc de transaction en cours.
L'espace_logique est le nom du tablespace dans lequel est créée la nouvelle table. S'il n'est pas indiqué, default_tablespace est consulté, sauf si la table est temporaire auquel cas temp_tablespaces est utilisé.
Une commande SELECT(7), TABLE ou VALUES(7), voire une commande EXECUTE(7) qui exécute un SELECT préparé, TABLE ou une requête VALUES.
Cette clause indique si les données produites par la requêtes doivent être copiées dans la nouvelle table. Si non, seule la structure de la table est copiée. La valeur par défaut est de copier les données.
Cette commande est fonctionnellement équivalente à SELECT INTO(7). Elle lui est cependant préférée car elle présente moins de risques de confusion avec les autres utilisations de la syntaxe SELECT INTO. De plus, CREATE TABLE AS offre plus de fonctionnalités que SELECT INTO.
Avant PostgreSQL™ 8.0, CREATE TABLE AS incluait toujours les OIDs dans la table créée. À partir de PostgresSQL™ 8.0, la commande CREATE TABLE AS autorise l'utilisateur à spécifier explicitement la présence des OID. En l'absence de précision, la variable de configuration default_with_oids est utilisée. À partir de PostgreSQL™ 8.1, la valeur par défaut de cette variable est « faux » ; le comportement par défaut n'est donc pas identique à celui des versions précédant la 8.0. Il est préférable que les applications qui nécessitent des OID dans la table créée par CREATE TABLE AS indiquent explicitement WITH (OIDS) pour s'assurer du comportement souhaité.
Créer une table films_recent contenant les entrées récentes de la table films :
CREATE TABLE films_recent AS SELECT * FROM films WHERE date_prod >= '2006-01-01';
Pour copier une table complètement, la forme courte utilisant la clause TABLE peut aussi être utilisée :
CREATE TABLE films2 AS TABLE films;
Créer une nouvelle table temporaire films_recents consistant des seules entrées récentes provenant de la table films en utilisant une instruction préparée. La nouvelle table a des OID et sera supprimée à la validation (COMMIT) :
PREPARE films_recents(date) AS
SELECT * FROM films WHERE date_prod > $1;
CREATE TEMP TABLE films_recents WITH (OIDS) ON COMMIT DROP AS
EXECUTE films_recents('2002-01-01');
CREATE TABLE AS est conforme au standard SQL. The following are nonstandard extensions :
Le standard requiert des parenthèses autour de la clause de la sous-requête ; elles sont optionnelles dans PostgreSQL™.
Dans le standard, la clause WITH [ NO ] DATA est requise alors que PostgreSQL la rend optionnelle.
PostgreSQL™ gère les tables temporaires d'une façon bien différente de celle du standard ; voir CREATE TABLE(7) pour les détails.
La clause WITH est une extension PostgreSQL™ ; ni les paramètres de stockage ni les OID ne sont dans le standard.
Le concept PostgreSQL™ des tablespaces ne fait pas partie du standard. Du coup, la clause TABLESPACE est une extension.