Importer un CSV dans PostgreSQL avec COPY et \copy

Pour importer dans PostgreSQL un fichier CSV présent sur votre ordinateur, utilisez \copy dans psql avec une liste explicite de colonnes et les options CSV. PostgreSQL vérifie les valeurs reçues par rapport aux types et contraintes de la table cible. Contrôlez les lignes importées avant de considérer l’opération comme terminée.

Ce guide utilise quatre enregistrements originaux et PostgreSQL 18.6. Nous avons exécuté 31 vérifications : imports réels de fichiers, virgules entre guillemets, retours à la ligne intégrés, texte UTF-8, valeurs NULL, en-têtes, erreurs, annulation de transactions et permissions. Les tests utilisent une base locale jetable et ne mesurent pas le débit d’un chargement massif.

Choisir où le fichier CSV sera lu

La référence de psql décrit \copy comme une opération côté client. Le processus psql ouvre le fichier local et transmet son contenu par la connexion à la base. L’instruction SQL COPY ... FROM 'filename' demande au serveur de base de données d’ouvrir lui-même le fichier.

Si psql tourne sur votre ordinateur et PostgreSQL ailleurs, le chemin de votre ordinateur s’utilise avec \copy. Si psql tourne dans un conteneur, le chemin dépend du système de fichiers accessible à ce conteneur. Notre guide PostgreSQL avec Docker Compose, en anglais, présente une configuration locale distincte.

Ces chemins d’accès aux fichiers sont différents. Dans notre test, un rôle restreint pouvait importer avec \copy en disposant de l’accès au schéma et du droit INSERT sur les colonnes importées. Il ne pouvait pas lancer un COPY lisant un fichier côté serveur. Le retrait du droit INSERT faisait aussi échouer \copy. L’accès au fichier côté client ne contourne pas les droits de la base.

Préparer un CSV dont les valeurs sont vérifiables

Enregistrez ce contenu dans orders.csv, en UTF-8. Conservez le retour à la ligne à l’intérieur de la note du troisième enregistrement. Le fichier contient quatre enregistrements de données, même si l’un occupe deux lignes physiques.

name,id,note,amount
"North, hub",1,,12.50
"Quote ""desk""",2,"",0.00
Café,3,"line one
line two",7.25
Plain,4,NULL,1.25

L’ordre du fichier est name, id, note, amount. La table ci-dessous commence volontairement par id. La commande d’import fournit la bonne correspondance par position.

Travaillez dans une base jetable nommée csv_demo, avec un rôle autorisé à créer le schéma de l’exercice. Le schéma csv_lab ne doit pas déjà exister. Enregistrez le bloc suivant dans 01-import.sql, à côté du CSV. Depuis ce répertoire, lancez psql -X -qAt -P 'null=<NULL>' -v ON_ERROR_STOP=1 -d csv_demo -f 01-import.sql, en ajoutant les options d’hôte et d’utilisateur nécessaires.

Les sorties ci-dessous utilisent le format non aligné de psql, sans en-têtes de résultats, et affichent les valeurs SQL NULL sous la forme <NULL>. Une session interactive ordinaire peut aussi afficher des bordures et des messages de commande. Gardez chaque commande \copy sur une seule ligne physique. C’est une commande psql ; un éditeur limité au SQL ne peut pas l’exécuter comme une instruction SQL.

Importer avec une liste explicite de colonnes

BEGIN;
CREATE SCHEMA csv_lab;
CREATE TABLE csv_lab.orders (
    id integer PRIMARY KEY,
    name text NOT NULL,
    note text,
    amount numeric(10,2) NOT NULL,
    source text NOT NULL DEFAULT 'csv'
);
\copy csv_lab.orders (name, id, note, amount) FROM 'orders.csv' WITH (FORMAT csv, HEADER true, ENCODING 'UTF8')
SELECT count(*) AS rows, sum(amount) AS total FROM csv_lab.orders;
COMMIT;

4|21.00

Le résultat contient quatre lignes pour un total de 21.00. La liste des colonnes importées exclut source : chaque enregistrement reçoit donc sa valeur par défaut. La transaction regroupe création et chargement ; ON_ERROR_STOP arrête le script si une opération échoue.

La référence de COPY documente les options de format et de colonnes. Ici, FORMAT csv active la lecture CSV, HEADER true ignore le premier enregistrement et ENCODING précise l’encodage UTF-8 du fichier. Le chemin est relatif au répertoire de travail de psql, pas automatiquement au répertoire du script.

Pour un autre fichier, examinez d’abord le séparateur, l’encodage, l’en-tête, les guillemets et les types de destination. Ne tentez pas de réparer un CSV en découpant chaque ligne sur les virgules : le premier nom contient une virgule, et la troisième note contient un retour à la ligne.

Vérifier NULL, chaînes vides, guillemets et valeurs exactes

Exécutez 02-inspect.sql avec les mêmes options psql :

SELECT id, name, note IS NULL AS note_is_null,
       length(note) AS note_length, amount, source
FROM csv_lab.orders ORDER BY id;
SELECT id, replace(note, chr(10), '<LF>') AS visible_note
FROM csv_lab.orders WHERE id IN (3, 4) ORDER BY id;

1|North, hub|t|<NULL>|12.50|csv
2|Quote "desk"|f|0|0.00|csv
3|Café|f|17|7.25|csv
4|Plain|f|4|1.25|csv
3|line one<LF>line two
4|NULL

La note du premier enregistrement est vide et sans guillemets : avec ces réglages CSV, elle devient SQL NULL. La note du deuxième est vide mais entre guillemets : elle devient une chaîne non NULL de longueur zéro. Le quatrième contient le texte littéral NULL, qui conserve ses quatre caractères. Le mot NULL n’est pas un marqueur spécial dans ce fichier.

La virgule de North, hub reste dans le nom. Les guillemets doublés du deuxième nom deviennent une paire de guillemets littéraux. Café conserve son caractère accentué. La troisième note compte 17 caractères et comprend un retour à la ligne ; la seconde requête remplace ce dernier par <LF> uniquement pour l’affichage.

Ces contrôles apportent davantage qu’un simple nombre de lignes. Une conversion réussie peut placer du texte valide dans la mauvaise colonne. Vérifiez des identifiants connus et des valeurs représentatives, notamment les champs vides et les champs entre guillemets.

Observer un échec avant de réessayer

Enregistrez ce second fichier sous bad-amount.csv. Il commence par une nouvelle ligne valide, suivie d’une valeur numérique invalide, puis d’une autre ligne valide.

name,id,note,amount
Valid first,5,ok,2.00
Invalid second,6,ok,not-a-number
Valid last,7,ok,3.00

Exécutez le bloc suivant dans un script distinct, 03-type-error.sql, en gardant ON_ERROR_STOP activé :

\copy csv_lab.orders (name, id, note, amount) FROM 'bad-amount.csv' WITH (FORMAT csv, HEADER true, ENCODING 'UTF8')

Notre exécution échoue avec le SQLSTATE 22P02, signalant une entrée numérique invalide pour not-a-number. Elle ne poursuit pas jusqu’au dernier enregistrement. Dans un nouvel appel, exécutez 04-check-after-error.sql :

SELECT count(*) AS rows, sum(amount) AS total FROM csv_lab.orders;
SELECT count(*) AS new_rows FROM csv_lab.orders WHERE id IN (5, 6, 7);

4|21.00
0

Les quatre lignes initiales et leur total restent inchangés. Aucun des identifiants 5, 6 ou 7 n’est visible, y compris celui de la ligne valide rencontrée avant l’erreur. Des tests distincts rejettent aussi un champ supplémentaire, un ordre de colonnes incorrect et une clé primaire dupliquée. La ligne valide précédant le doublon est elle aussi annulée.

Si l’import fait partie d’une transaction explicite plus large, son échec affecte cette transaction. Un test supplémentaire insère l’identifiant 99, tente le mauvais import, puis quitte psql après l’erreur : l’INSERT précédent est annulé. Dans une session interactive, exécutez ROLLBACK avant de recommencer après une erreur de transaction.

Examinez la colonne signalée et l’enregistrement concerné, corrigez la source et vérifiez la destination avant de réessayer. Relancer un import déjà réussi n’est pas forcément sans conséquence : COPY ajoute des lignes, et des clés répétées peuvent provoquer un échec. Ce guide conserve le comportement par défaut d’arrêt sur erreur, sans ignorer les enregistrements mal formés.

Ne pas confondre HEADER true et correspondance des noms

Enregistrez swapped-header.csv avec ces deux colonnes :

second,first
B,A

Le script suivant, 05-header-true.sql, importe dans deux colonnes textuelles placées dans l’ordre inverse :

CREATE TABLE csv_lab.header_probe (first text, second text);
\copy csv_lab.header_probe (first, second) FROM 'swapped-header.csv' WITH (FORMAT csv, HEADER true)
SELECT first, second FROM csv_lab.header_probe;

B|A

Le résultat est first=B et second=A. HEADER true ignore les noms ; il ne réorganise pas les valeurs. Comme les deux champs sont textuels, la conversion ne révèle pas l’erreur.

Dans PostgreSQL 18, version testée ici, HEADER MATCH vérifie l’en-tête par rapport à l’ordre des colonnes importées. Le script distinct 06-header-match.sql échoue avec le SQLSTATE 22P04 :

\copy csv_lab.header_probe (first, second) FROM 'swapped-header.csv' WITH (FORMAT csv, HEADER MATCH)

La ligne existante de header_probe reste inchangée. Un test distinct avec l’en-tête original de orders.csv et une liste de colonnes correspondante réussit. HEADER MATCH valide l’ordre ; il ne réorganise pas les champs. Vérifiez la disponibilité de l’option dans votre version de PostgreSQL.

Supprimer les objets de l’exercice

Après les exemples, 07-cleanup.sql supprime uniquement les tables et le schéma créés pour ce guide :

DROP TABLE csv_lab.header_probe;
DROP TABLE csv_lab.orders;
DROP SCHEMA csv_lab;

Notre programme de vérification supprime aussi son rôle de test, arrête le serveur isolé et efface le fichier de mot de passe temporaire. L’archive téléchargeable contient les CSV, les scripts SQL, les résultats capturés et le programme de vérification, sans répertoire de base ni identifiants de connexion.

Pour un import réel, consignez le fichier source, la correspondance des colonnes, le nombre de lignes acceptées et les résultats de validation. Un CSV transporte des valeurs tabulaires ; il ne reproduit pas toute la structure ni les permissions d’une base. Consultez notre guide de sauvegarde et de restauration PostgreSQL, en anglais, si votre objectif est de restaurer une base.