- Club d'entraide des développeurs francophones

Documentation PostgreSQL 8.2.5 -

Commandes SQL

Précédent
COMMIT PREPARED
Suivant
CREATE AGGREGATE
Niveau supérieur.
Sommaire

COPY

COPY — Copier des données depuis/vers un fichier vers/depuis une table

Synopsis

COPY nomtable [ ( colonne [, ...] ) ]
    FROM { 'nomfichier' | STDIN }
    [ [ WITH ] 
        [ BINARY ] 
        [ OIDS ]
        [ DELIMITER [ AS ] 'délimiteur' ]
        [ NULL [ AS ] 'chaîne NULL' ]
        [ CSV [ HEADER ]
              [ QUOTE [ AS ] 'guillemet' ] 
              [ ESCAPE [ AS ] 'échappement' ]
              [ FORCE NOT NULL colonne [, ...] ]

COPY { nomtable [ ( colonne [, ...] ) ] | ( requête ) }
    TO { 'nomfichier' | STDOUT }
    [ [ WITH ] 
        [ BINARY ]
        [ HEADER ]
        [ OIDS ]
        [ DELIMITER [ AS ] 'délimiteur' ]
        [ NULL [ AS ] 'chaîne NULL' ]
        [ CSV [ HEADER ]
              [ QUOTE [ AS ] 'guillemet' ] 
              [ ESCAPE [ AS ] 'échappement' ]
              [ FORCE QUOTE colonne [, ...] ]

Description

COPY transfère des données entre les tables de PostgreSQL™ et les fichiers du système de fichiers standard. COPY TO copie le contenu d'une table vers un fichier tandis que COPY FROM copie des données depuis un fichier vers une table (ajoutant les données à celles déjà dans la table). COPY TO peut aussi copier le résultat d'une requête SELECT .

Si une liste de colonnes est précisée, COPY ne copie que les données des colonnes spécifiées vers ou depuis le fichier. COPY FROM insère les valeurs par défaut des colonnes qui ne sont pas précisées dans la liste.

Si un nom de fichier est précisé, COPY lit ou écrit directement dans le fichier. Ce fichier doit être accessible par le serveur et son nom doit être spécifié du point de vue du serveur. Si STDIN ou STDOUT est indiqué, les données sont transmises au travers de la connexion entre le client et le serveur.

Paramètres

nomtable: Le nom de la table (éventuellement qualifié du nom du schéma).
colonne: Une liste optionnelle de colonnes à copier. Sans précision, toutes les colonnes de la table seront copiées.
requête

Affichage

En cas de succès, une commande COPY renvoie une balise de la forme

COPY nombre

Le nombre correspond au nombre de lignes copiées.

Notes

COPY ne peut être utilisé qu'avec des tables réelles, pas avec des vues. Néanmoins, vous pouvez écrire COPY (SELECT * FROM nom_vue ) TO ....

Le mot clé BINARY impose que les données soient stockées/lues dans un format binaire, non en texte. C'est quelque peu plus rapide qu'en mode texte, mais un fichier binaire est moins portable au travers des architectures machine et des versions de PostgreSQL™.

Le droit SELECT est requis sur la table dont les valeurs sont lues par COPY TO et le droit INSERT sur la table dont les valeurs sont insérées par COPY FROM .

Les fichiers nommés dans une commande COPY sont lus ou écrits directement par le serveur, non par l'application cliente. De ce fait, la machine hébergeant le serveur de bases de données doit les héberger ou pouvoir y accéder. L'utilisateur PostgreSQL™ (l'identifiant de l'utilisateur qui exécute le serveur), non le client, doit pouvoir y accéder et les lire ou les modifier. L'utilisation de COPY avec un fichier n'est autorisé qu'aux superutilisateurs de la base de données car COPY autorise la lecture et l'écriture de tout fichier accessible au serveur.

Il ne faut pas confondre COPY et l'instruction \copy de psql. \copy appelle COPY FROM STDIN ou COPY TO STDOUT , puis lit/stocke les données dans un fichier accessible au client psql. L'accès au fichier et les droits d'accès dépendent alors du client et non du serveur.

Il est recommandé que le chemin absolu du fichier utilisé dans COPY soit toujours précisé. Ceci est assuré par le serveur dans le cas d'un COPY TO mais, pour les COPY FROM , il est possible de lire un fichier spécifié par un chemin relatif. Le chemin est interprété relativement au répertoire de travail du processus serveur (habituellement dans le répertoire des données), pas par rapport au répertoire de travail du client.

COPY FROM appelle tous les déclencheurs et contraintes de vérification sur la table de destination, mais pas les règles.

L'entrée et la sortie de COPY sont sensibles à datestyle. Pour assurer la portabilité vers d'autres installations de PostgreSQL™ qui éventuellement utilisent des paramétrages datestyle différents de ceux par défaut, il est préférable de configurer datestyle en ISO avant d'utiliser COPY TO .

COPY stoppe l'opération à la première erreur. Si cela ne porte pas à conséquence dans le cas d'un COPY TO , il en va différemment dans le cas d'un COPY FROM . Dans ce cas, la table cible a déjà reçu les lignes précédentes. Ces lignes ne sont ni visibles, ni accessibles, mais occupent de l'espace disque. Il peut en résulter une perte importante d'espace disque si l'échec se produit lors d'une copie volumineuse. L'espace perdu peut alors être récupéré avec la commande VACUUM .

Formats de fichiers

Format texte

Lorsque COPY est utilisé sans l'option BINARY ou l'option CSV, les données sont lues ou écrites dans un fichier texte, chauqe ligne correspondant à une ligne de la table. Les colonnes sont séparées, dans une ligne, par le caractère de délimitation. Les valeurs des colonnes sont des chaînes, engendrées par la fonction de sortie ou utilisables par celle d'entrée, correspondant au type de données des attributs. La chaîne de spécification des valeurs NULL est utilisée en lieu et place des valeurs nulles. COPY FROM lève une erreur si une ligne du fichier ne contient pas le nombre de colonnes attendues. Si OIDS est précisé, l'OID est lu ou écrit dans la première colonne, avant celles des données utilisateur.

La fin des données peut être représentée par une ligne ne contenant qu'un antislash et un point (\.). Ce marqueur de fin de données n'est pas nécessaire lors de la lecture d'un fichier, la fin du fichier tenant ce rôle. Il n'est réellement nécessaire que lors d'une copie de données vers ou depuis une application cliente qui utilise un protocole client antérieur au 3.0.

Les caractères antislash (\) peuvent être utilisés dans les données de COPY pour échapper les caractères qui, sans cela, seraient considérés comme des délimiteurs de ligne ou de colonne. Les caractères suivants, en particulier, doivent être précédés d'un antislash s'ils apparaissent dans la valeur d'une colonne : l'antislash lui-même, le saut de ligne, le retour chariot et le délimiteur courant.

La chaîne NULL spécifiée est envoyée par COPY TO sans ajout d'antislash ; au contraire, COPY FROM teste l'entrée au regard de la chaîne NULL avant la suppression des antislash. Ainsi, une chaîne NULL telle que \N ne peut pas être confondue avec la valeur de donnée réelle \N (représentée dans ce cas par \\N).

Les séquences spéciales suivantes sont reconnues par COPY FROM :

Séquence	Représente
`\b`	Retour arrière ( backspace ) (ASCII 8)
`\f`	Retour chariot (ASCII 12)
`\n`	Nouvelle ligne (ASCII 10)
`\r`	Retour chariot (ASCII 13)
`\t`	Tabulation (ASCII 9)
`\v`	Tabulation verticale (ASCII 11)
`\` `chiffres`	Antislash suivi d'un à trois chiffres en octal représente le caractère qui possède ce code numérique
`\x` `digits`	Antislash `x` suivi d'un ou deux chiffres hexadécimaux représente le caractère qui possède ce code numérique

Actuellement, COPY TO n'émet pas de séquence octale ou hexadécimale mais utilise les autres séquences listées ci-dessus pour les caractères de contrôle.

Tout autre caractère précédé d'un antislash se représente lui-même. Cependant, il faut faire attention à ne pas ajouter d'antislash qui ne soit pas absolument nécessaire afin d'éviter le risque d'obtenir accidentellement une correspondance avec le marqueur de fin de données (\.) ou la chaîne NULL (\N par défaut) ; ces chaînes sont reconnues avant tout traitement des antislashs.

Il est fortement recommandé que les applications qui engendrent des données COPY convertissent les données de nouvelle ligne et de retour chariot par les séquences respectives \n et \r. A l'heure actuelle, il est possible de représenter un retour chariot par un antislash et un retour chariot, et une nouvelle ligne par un antislash et une nouvelle ligne. Cependant, il n'est pas certain que ces représentations soient encore acceptées dans les prochaines versions. Celles-ci sont, de plus, extrêmement sensibles à la corruption si le fichier de COPY est transféré sur d'autres plateformes (d'un Unix vers un Windows ou inversement, par exemple).

COPY TO termine chaque ligne par une nouvelle ligne de style Unix (« \n »). Les serveurs fonctionnant sous Microsoft Windows engendrent un retour chariot/nouvelle ligne (« \r\n »), mais uniquement lorsque les données engendrées par COPY sont envoyées dans un fichier sur le serveur. Pour des raisons de cohérence entre les plateformes, COPY TO STDOUT envoie toujours « \n » quelque soit la plateforme du serveur. COPY FROM sait gérer les lignes terminant par une nouvelle ligne, un retour chariot ou un retour chariot suivi d'une nouvelle ligne. Afin de réduire les risques d'erreurs engendrées par des nouvelles lignes ou des retours chariot non précédés d'antislash, considéré de fait comme des données, COPY FROM émet un avertissement si les fins de lignes ne sont pas toutes identiques.

Format CSV

Ce format est utilisé pour importer et exporter des données au format de fichier CSV (acronyme de Comma Separated Value , littéralement valeurs séparées par des virgules). Ce format est utilisé par un grand nombre de programmes, tels les tableurs. À la place de l'échappement utilisé par le mode texte standard de PostgreSQL™, il produit et reconnaît le mécanisme d'échappement habituel de CSV.

Les valeurs de chaque enregistrement sont séparées par le caractère DELIMITER. Si la valeur contient ce caractère, le caractère QUOTE, la chaîne NULL, un retour chariot ou un saut de ligne, la valeur complète est préfixée et suffixée par le caractère QUOTE. De plus, toute occurrence du caractère QUOTE ou du caractère ESCAPE est précédée du caractère d'échappement. FORCE QUOTE peut également être utilisé pour forcer les guillemets lors de l'affichage de valeur non-NULL dans des colonnes spécifiques.

Le format CSV ne permet pas de distinguer de façon standard une valeur NULL d'une chaîne vide. La commande COPY de PostgreSQL™ gère cela avec les guillemets. Un NULL est affiché par la chaîne NULL et n'est jamais entre guillemets. Ainsi, avec les paramètres par défaut, NULL est écrit comme une chaîne vide sans guillemets, alors qu'une chaîne vide est écrite avec des guillemets doubles (""). La lecture des valeurs suit des règles similaires. L'utilisation de FORCE NOT NULL permet de prévenir les comparaisons d'entrées NULL pour des colonnes particulières.

L'antislash n'est pas un caractère spécial dans le format CSV. De ce fait, le marqueur de fin de données, \., peut apparaître dans les donnée. Afin d'éviter toute mauvaise interprétation, une valeur \. qui apparaît seule sur une ligne est automatiquement placée entre guillemets en sortie. En entrée, si elle est entre guillemets, elle n'est pas interprétée comme un marqueur de fin de données. Lors du chargement d'un fichier qui ne contient qu'une colonne, dont les valeurs ne sont pas placées entre guillemets, créé par une autre application, qui contient une valeur \., il est nécessaire de placer cette valeur entre guillemets.

Note

Dans le mode CSV, tous les caractères sont significatifs. Une valeur entre guillemets entourée d'espaces ou de tout autre caractère différent de DELIMITER inclut ces caractères. Cela peut être source d'erreurs en cas d'import de données à partir d'un système qui complète les lignes CSV avec des espaces fines pour atteindre une longueur fixée. Dans ce cas, il est nécessaire de pré-traiter le fichier CSV afin de supprimer les espaces de complètement avant d'insérer les données dans PostgreSQL™.

Note

Le mode CSV sait reconnaître et produire des fichiers CSV dont les valeurs entre guillemets contiennent des retours chariot et des sauts de ligne. De ce fait, les fichiers ne contiennent pas strictement une ligne par ligne de table comme les fichiers du mode texte.

Note

Beaucoup de programmes produisent des fichiers CSV étranges et parfois pervers ; le format de fichier est donc plus une convention qu'un standard. Il est alors possible de rencontrer des fichiers que ce mécanisme ne sait pas importer. De plus, COPY peut produire des fichiers inutilisables par d'autres programmes.

Format binaire

Le format de fichier utilisé pour COPY BINARY a été modifié dans PostgreSQL™ 7.4. Le nouveau format consiste en un en-tête de fichier, des tuples (éventuellement aucun) qui contiennent les données de la ligne et une queue de fichier. Les en-têtes et les données sont désormais dans l'ordre des octets réseau.

Entête du fichier

L'en-tête du fichier est constitutée de 15 octets de champs fixes, suivis par une aire d'extension de l'en-tête de longueur variable. Les champs fixes sont :

Signature

séquence de 11 octets PGCOPY\n\377\r\n\0 -- l'octet zéro est une partie obligatoire de la signature. La signature est conçue pour permettre une identification aisée des fichiers qui ont été déteriorés par un transfert non respectueux des huit bits. Cette signature est modifiée par les filtres de traduction de fin de ligne, la suppression des octets zéro, la suppression des bits de poids forts ou la modification de la parité.

Champs de commutateurs

masque entier de 32 bits décrivant les aspects importants du format de fichier. Les bits sont numérotés de 0 (LSB, ou Least Significant Bit , bit de poids faible) à 31 (MSB, ou Most Significant Bit , bit de poids fort). Ce champ est stocké dans l'ordre réseau des octets (l'octet le plus significatif en premier), comme le sont tous les champs entier utilisés dans le format de fichier. Les bits 16 à 31 sont réservés aux problèmes critiques de format de fichier ; tout lecteur devrait annuler l'opération s'il trouve un bit inattendu dans cet ensemble. Les bits 0 à 15 sont réservés pour signaler les problèmes de compatibilité de formats ; un lecteur devrait simplement ignorer les bits inattendus dans cet ensemble. Actuellement, seul un bit est défini, le reste doit être à zéro :

Bit 16: si 1, les OID sont inclus dans la donnée ; si 0, non

Longueur de l'aire d'extension de l'en-tête

entier sur 32 bits, longueur en octets du reste de l'en-tête, octets de stockage de la longueur non-compris. À l'heure actuelle ce champ vaut zéro. La première ligne suit immédiatement. De futures modifications du format pourraient permettre la présence de données supplémentaires dans l'en-tête. Tout lecteur devrait ignorer silencieusement toute donnée de l'extension de l'en-tête qu'il ne sait pas traitée.

L'aire d'extension de l'en-tête est prévue pour contenir une séquence de morceaux s'auto-identifiant. Le champ de commutateurs n'a pas pour but d'indiquer aux lecteurs ce qui se trouve dans l'aire d'extension. La conception spécifique du contenu de l'extension de l'en-tête est pour une prochaine version.

Cette conception permet l'ajout d'en-têtes compatible (ajout de morceaux d'extension d'en-tête, ou initialisation des octets commutateurs de poids faible) et les modifications non compatibles (initialisation des octets commutateurs de poids fort pour signaler de telles modifications, et ajout des données de support dans l'aire d'extension si nécessaire).

Tuples

Chaque tuple débute par un compteur, entier codé sur 16 bits, représentant le nombre de champs du tuple. (Actuellement, tous les tuples d'une table ont le même compteur, mais il est probable que cela ne soit pas toujours le cas.) On trouve ensuite, répété pour chaque champ du tuple, un mot de 32 bits annonçant le nombre d'octets de stockage de la donnée qui suivent. (Ce mot n'inclut pas sa longueur propre et peut donc être nul.) -1, cas spécial, indique une valeur de champ NULL. Dans ce cas, aucun octet de valeur ne suit.

Il n'y a ni complètement d'alignement ni toute autre donnée supplémentaire entre les champs.

Actuellement, toutes les valeurs d'un fichier COPY BINARY sont supposées être dans un format binaire (code de format). Il est probable qu'une extension future ajoute un champ d'en-tête autorisant la spécification de codes de format par colonne.

La consulation du code source de PostgreSQL™, et en particulier les fonctions *send et *recv associées à chaque type de données de la colonne, permet de déterminer le format binaire approprié à la donnée réelle. Ces fonctions se situent dans le répertoire src/backend/utils/adt/ des sources.

Lorsque les OID sont inclus dans le fichier, le champ OID suit immédiatement le compteur de champ. C'est un champ normal, à ceci près qu'il n'est pas inclus dans le compteur. En fait, il contient un mot de stockage de la longueur -- ceci permet de faciliter le passage d'OID sur quatre octets aux OID sur huit octets et permet d'afficher les OID comme étant NULL en cas de besoin.

Queue du fichier

La fin du fichier consiste en un entier sur 16 bits contenant -1. Cela permet de le distinguer aisément du compteur de champs d'un tuple.

Il est souhaitable que le lecteur rapporte une erreur si le mot compteur de champ ne vaut ni -1 ni le nombre attendu de colonnes. Cela assure une vérification supplémentaire d'une éventuelle désynchronisation d'avec les données.

Exemples

Copier une table vers le client en utilisant la barre verticale (|) comme délimiteur de champ :

COPY pays TO STDOUT WITH DELIMITER '|';

Copier des données d'un fichier vers la table pays :

COPY pays FROM '/usr1/proj/bray/sql/pays_donnees';

Pour copier dans un fichier les pays dont le nom commence par 'A' :

COPY (SELECT * FROM pays WHERE nom_pays LIKE 'A%') TO '/usr1/proj/bray/sql/une_liste_de_pays.copy';

Exemple de données convenables pour une copie vers une table depuis STDIN :

AF      AFGHANISTAN
AL      ALBANIE
DZ      ALGERIE
ZM      ZAMBIE
ZW      ZIMBABWE

L'espace sur chaque ligne est en fait un caractère de tabulation.

Les même données, extraites au format binaire. Les données sont affichées après filtrage au travers de l'outil Unix od -c . La table a trois colonnes ; la première est de type char(2), la deuxième de type text et la troisième de type integer. Toutes les lignes ont une valeur NULL sur la troisième colonne.

0000000   P   G   C   O   P   Y  \n 377  \r  \n  \0  \0  \0  \0  \0  \0
0000020  \0  \0  \0  \0 003  \0  \0  \0 002   A   F  \0  \0  \0 013   A
0000040   F   G   H   A   N   I   S   T   A   N 377 377 377 377  \0 003
0000060  \0  \0  \0 002   A   L  \0  \0  \0 007   A   L   B   A   N   I
0000100   E 377 377 377 377  \0 003  \0  \0  \0 002   D   Z  \0  \0  \0
0000120 007   A   L   G   E   R   I   E 377 377 377 377  \0 003  \0  \0
0000140  \0 002   Z   M  \0  \0  \0 006   Z   A   M   B   I   E 377 377
0000160 377 377  \0 003  \0  \0  \0 002   Z   W  \0  \0  \0  \b   Z   I
0000200   M   B   A   B   W   E 377 377 377 377 377 377

Compatibilité

Il n'existe pas d'instruction COPY dans le standard SQL.

La syntaxe suivante, utilisée avant PostgreSQL™ version 7.3, est toujours supportée :

COPY [ BINARY ] nomtable [ WITH OIDS ]
    FROM { 'nomfichier' | STDIN }
    [ [USING] DELIMITERS 'délimiteur' ]
    [ WITH NULL AS 'chaîne NULL' ]

COPY [ BINARY ] nomtable [ WITH OIDS ]
    TO { 'nomfichier' | STDOUT }
    [ [USING] DELIMITERS 'délimiteur' ]
    [ WITH NULL AS 'chaîne NULL' ]

COMMIT PREPARED

CREATE AGGREGATE Suivant