COPY

Nom

COPY -- copie des données entre un fichier et une table

Synopsis

COPY nomtable [ ( colonne [, ...] ) ]
    FROM { 'nomfichier' | STDIN }
    [ [ WITH ] 
          [ BINARY ] 
          [ OIDS ]
          [ DELIMITER [ AS ] 'délimiteur' ]
          [ NULL [ AS ] 'chaîne
NULL' ] ]

COPY nomtable [ ( colonne [, ...] ) ]
    TO { 'nomfichier' | STDOUT }
    [ [ WITH ] 
          [ BINARY ]
          [ OIDS ]
          [ DELIMITER [ AS ] 'délimiteur' ]
          [ NULL [ AS ] 'chaîne
NULL' ] ]

Description

COPY déplace 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 dans un fichier alors que COPY FROM copie des données à partir d'un fichier dans une table (ajoutant les données à ce qui se trouve déjà dans la table).

Si une liste de colonnes est spécifiée, COPY copiera seulement les données des colonnes spécifiées vers ou à partir du fichier. S'il y a des colonnes dans la table qui ne se trouvent pas dans la liste de colonnes, COPY FROM insérera les valeurs par défaut de ces colonnes.

COPY avec un nom de fichier indique au serveur PostgreSQL de lire directement à partir d'un fichier ou d'écrire dans un fichier. Le fichier doit être accessible du serveur et le nom doit être spécifié à partir du point de vue du serveur. Lorsque STDIN ou STDOUT est indiqué, les données sont transmises via la connexion entre le client et le serveur.

Paramètres

nomtable

Le nom d'une table existante (pouvant être qualifié avec le nom du schéma).

colonne

Une liste optionnelle de colonnes à copier. Si aucune liste n'est donnée, toutes les colonnes seront utilisées.

nomfichier

Le chemin absolu du fichier en entrée ou en sortie.

STDIN

Spécifie que l'entrée provient de l'application cliente.

STDOUT

Spécifie que la sortie va vers l'application cliente.

BINARY

Fait que toutes les données sont stockées ou lues au format binaire plutôt qu'en texte. Vous ne pouvez pas spécifier les options DELIMITER ou NULL en mode binaire.

OIDS

Demande la copie des OID pour chaque ligne. (Une erreur est levée si OIDS est spécifié pour une table qui ne possède pas d'OID.)

délimiteur

Le caractère simple qui sépare les colonnes pour chaque ligne d'un fichier. La valeur par défaut est le caractère de tabulation.

chaîne NULL

La chaîne qui représente une valeur NULL. Par défaut, elle vaut \N (antislash-N). Vous pourriez préférer une chaîne vide par exemple.

Note : Sur un COPY FROM, tout élément de données correspondant à cette chaîne sera stocké comme valeur NULL, donc vous devriez vous assurer que vous utilisez la même chaîne que celle que vous avez utilisé avec COPY TO.

Notes

COPY peut seulement être utilisé avec des tables réelles, pas avec des vues.

Le mot clé BINARY fait que toutes les données sont stockées/lues dans le format binaire plutôt qu'en texte. Il est un peu plus rapide que le mode texte standard mais un fichier binaire est moins portable au travers des architectures machine et des versions de PostgreSQL.

Vous devez avoir le droit SELECT 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, et non pas par l'application cliente. Du coup, ils doivent résider ou être accessible par la machine du serveur de bases de données. Ils doivent être accessibles et lisibles ou modifiables par l'utilisateur PostgreSQL (l'identifiant de l'utilisateur qui a exécuté le serveur), et non pas par le client. COPY nommant un fichier est seulement autorisé pour les superutilisateurs de la base de données car il autorise la lecture ou l'écriture de tout fichier auquel a accès le serveur.

Ne confondez pas COPY avec l'instruction \copy de psql. \copy appelle COPY FROM STDIN ou COPY TO STDOUT, puis récupère/stocke la donnée dans un fichier accessible au client psql. Du coup, l'accès au fichier et les droits d'accès dépendent du client plutôt que du serveur quand \copy est utilisé.

Il est recommandé que le nom du fichier utilisé dans COPY soit toujours utilisé avec un chemin absolu. Ceci est renforcé par le serveur dans le cas d'un COPY TO mais, pour les COPY FROM, vous devez avoir l'option de lire à partir d'un fichier spécifié par un chemin relatif. Le chemin sera interprété de façon relative au répertoire de travail du processus serveur (quelque part en-dessous du répertoire de données), pas dans le répertoire de travail du client.

COPY FROM appellera tout déclencheur et vérifiera les contraintes sur la table de destination. Néanmoins, il n'appellera pas les règles.

COPY stoppe l'opération à la première erreur. Ceci ne devrait apporter aucun problème dans le cas d'un COPY TO mais la table cible aura déjà reçu des lignes précédentes dans un COPY FROM. Ces lignes ne seront pas visibles ou accessibles mais elle occuperont toujours de l'espace disque. Ceci pourrait totaliser une perte considérable d'espace disque si l'échec arrivait lors d'une grande opération de copie. Vous pourriez souhaiter appeler VACUUM pour récupérer l'espace perdu.

Formats de fichiers

Format texte

Quand COPY est utilisé sans l'option BINARY, les données lues ou écrites sont dans un fichier avec une ligne de données par ligne de la table. Les colonnes d'une ligne sont séparées par le caractère délimiteur. Les valeurs des colonnes sont des chaînes générées par la fonction de sortie ou acceptables par la fonction d'entrée de chaque type de données d'attribut. La chaîne NULL spécifiée est utilisée à la place des colonnes NULL. COPY FROM lèvera une erreur si une des lignes du fichier en entrée contient plus ou moins de colonnes qu'attendues. Si OIDS est spécifié, l'OID est lu ou écrit dans la première colonne, précédant ainsi les colonnes de données de l'utilisateur.

La fin des données peut être représentée par une simple ligne contenant juste un antislash et un point (\.). Un marqueur de fin de données n'est pas nécessaire lors de la lecture d'un fichier car la fin du fichier sert bien  il est seulement nécessaire lors de la copie de données vers ou à partir d'une application cliente en utilisant le protocole client pre-3.0.

Les caractères antislash (\) pourraient être utilisés dans les données COPY pour mettre entre guillemets les données de caractères qui pourraient sinon être prises comme délimiteurs de ligne ou de colonne. En particulier, les caractères suivants doivent être précédés par un antislash s'ils apparaissent comme faisant partie de la valeur d'une colonne : antislash lui-même, nouvelle ligne, retour chariot et caractère délimiteur actuel.

La chaîne NULL spécifiée est envoyée par COPY TO sans ajout d'antislash ; au contraire, COPY FROM fait correspondre l'entrée avec la chaîne NULL avant de supprimer les antislash. Du coup, une chaîne NULL telle que \N ne peut pas être confondu avec la valeur de donnée réelle \N (qui serait représentée par \\N).

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

SéquenceReprésenté
\bSuppression (ASCII 8)
\fRetour chariot (ASCII 12)
\nNouvelle ligne (ASCII 10)
\rRetour chariot (ASCII 13)
\tTabulation (ASCII 9)
\vTabulation verticale (ASCII 11)
\chiffresUn antislash suivi par un à trois chiffres en octal spécifie le caractère ayant ce code numérique

Actuellement, COPY TO n'émettra jamais une séquence octale mais utilisera les autres séquences listées ci-dessus pour les caractères de contrôle.

Tout autre caractère avec un antislash se représentera lui-même. Néanmoins, attention à l'ajout non nécessaire d'antislash car cela pourrait produire accidentellement une correspondance de chaîne avec le marqueur de fin de données (\.) ou la chaîne NULL (\N par défaut). Ces chaînes seront reconnues avant tout traitement des antislashs.

Il est fortement recommandé que les applications générant des données COPY convertissent les nouvelles lignes et retours chariot avec les séquences \n et \r, respectivement. À présent, il est possible de représenter un retour chariot par un antislash et un retour chariot, et de représenter une nouvelle ligne par un antislash et une nouvelle ligne. Néanmoins, ces représentations pourraient ne pas être acceptées dans les prochaines versions. Elles sont aussi hautement vulnérables à la corruption si le fichier COPY est transféré via différentes machines (par exemple, à partir d'un Unix vers un Windows ou vice versa).

COPY TO terminera chaque ligne avec une nouvelle ligne style Unix (<< \n >>). Les serveurs fonctionnant avec MS Windows terminent la ligne avec un retour chariot/nouvelle ligne (<< \r\n >>) mais seulement pour COPY dans un fichier serveur ; pour des raisons de cohérence au niveau des plateformes, COPY TO STDOUT envoie toujours << \n >> quelque soit la plateforme du serveur. COPY FROM peut gérer des fin de lignes avec une nouvelle ligne, un retour chariot ou un retour chariot suivi d'une nouvelle ligne. Pour réduire les risques d'erreurs dûes à une nouvelle ligne ou un retour chariot sans antislash qui étaient des données, COPY FROM se plaindra si les fins de ligne dans l'entrée ne sont pas identiques.

Format binaire

Le format de fichier utilisé pour COPY BINARY a été modifié dans PostgreSQL 7.4. Le nouveau format consiste en un fichier d'en-tête, zéro ou plusieurs lignes contenant la donnée de la ligne et une queue de fichier. Les en-têtes et les données sont maintenant dans l'ordre d'octets du réseau.

En-tête du fichier

Le fichier d'en-tête consiste en 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 --- notez que l'octet zéro est une partie requise 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 qui ne s'est pas fait sur huit bits. Cette signature sera modifiée par des filtres de traduction de fin de ligne, suppression des octets zéro, suppression des bits de poids forts ou modification de parité.)

Champs de commutateurs

masque entier de 32 bits dénotant les aspects importants du format de fichier. Les bits sont numérotés de 0 (LSB) à 31 (MSB). Notez que ce champ est stocké dans l'ordre des octets du réseau (l'octet le plus significatif en premier), comme le sont tous les champs d'entier utilisés dans le format de fichier. Les bits 16 à 31 sont réservés pour dénoter les problèmes critiques de format de fichier ; un 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 des problèmes de compatibilité de formats ; un lecteur devrait simplement ignorer les bits inattendus dans cet ensemble. Actuellement, seul un bit est défini et 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 sur le reste de l'en-tête, ne s'incluant pas lui-même. Actuellement, c'est zéro et la première ligne suit immédiatement. Les modifications futures du format pourraient permettre la présence de données supplémentaires dans l'en-tête. Un lecteur devrait passer silencieusement toute donnée dans l'extension de l'en-tête s'il ne sait pas quoi faire avec.

L'aire d'extension de l'en-tête devrait contenir une séquence de morceaux qui s'identifient eux-même. Le champ des 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 laissée à une prochaine version.

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

Lignes

Chaque ligne commence avec un compteur sur 16 bits du nombre de champs dans la ligne. (Actuellement, toutes les lignes dans une table auront le même compte mais ceci pourrait ne pas être toujours vrai.) Ensuite, répété pour chaque champ de la ligne, il y a un mot de 32 bits suivi par autant d'octets que de données. (Le mot clé n'inclut pas sa propre longueur et peut donc valoir zéro.) Comme cas spécial, -1 indique une valeur de champ NULL. Aucun octet de valeur ne suit dans le cas NULL.

Il n'y a pas d'ajout pour l'alignement ou toute autre donnée supplémentaire entre les champs.

Actuellement, toutes les valeurs dans un fichier COPY BINARY sont supposées être dans un format binaire (format code un). Une future extension pourrait ajouter un champ d'en-tête qui autorise la spécification des codes de format par colonne.

Pour déterminer le format binaire approprié pour la donnée réelle, vous devriez consulter le source de PostgreSQL, en particulier les fonctions *send et *recv pour chaque type de données de la colonne (typiquement ces fonctions se trouvent dans le répertoire src/backend/utils/adt/ des sources).

Si les OID sont inclus dans le fichier, le champ OID est immédiatement suivi du compteur de champ. C'est un champ normal sauf qu'il n'est pas inclus dans le champ compteur. En particulier, il a une longueur d'un mot --- ceci permettra la gestion d'OID à quatre octets plutôt que huit sans trop de peine et permettra l'affichage des OID comme NULL si cela se révèle intéressant.

Queue du fichier

La fin du fichier consiste en un entier sur 16 bits contenant -1. Ceci est facilement distingué du compteur de champ d'une ligne.

Un lecteur devrait rapporter une erreur si un mot de comptage de champ est soit -1 soit le nombre attendu de colonnes. Ceci fournit une vérification supplémentaire sur quelque chose pouvant être hors synchronisation avec les données.

Exemples

L'exemple suivant copie une table vers le client en utilisant la barre verticale (|) comme délimiteur de champ :

COPY pays TO STDOUT WITH DELIMITER '|';

Pour copier des données de la table pays vers un fichier :

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

Voici un exemple de données convenable pour une table provenant de STDIN :

AF      AFGHANISTAN
AL      ALBANIA
DZ      ALGERIA
ZM      ZAMBIA
ZW      ZIMBABWE

Notez que l'espace blanc sur chaque ligne est en fait un caractère de tabulation.

Ce qui suit représente les même données, au format binaire. La donnée est affichée après filtrage à travers l'outil Unix od -c. La table a trois colonnes ; la première est 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   A 377 377 377 377  \0 003  \0  \0  \0 002   D   Z  \0  \0  \0
0000120 007   A   L   G   E   R   I   A 377 377 377 377  \0 003  \0  \0
0000140  \0 002   Z   M  \0  \0  \0 006   Z   A   M   B   I   A 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 était utilisée avant PostgreSQL version 7.3 et 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' ]