Comment puis-je mettre à jour efficacement les lignes d'une table Postgres à partir d'un fichier CSV ?

Optimisation des mises à jour Postgres avec des données CSV

La mise à jour efficace des tables Postgres avec les données provenant de fichiers CSV est cruciale pour la gestion des données. Disons que vous avez un tableau (id, banane, pomme) et un fichier CSV avec des valeurs banana mises à jour. Le défi est de mettre à jour uniquement la colonne banana sans modifier la colonne apple.

Tirer parti de COPY et UPDATE pour des mises à jour efficaces

L'approche optimale consiste à utiliser COPY pour importer le CSV dans une table temporaire, puis à effectuer un UPDATE basé sur la colonne id. Voici le processus :

<code class="language-sql">CREATE TEMP TABLE tmp_x (id int, apple text, banana text); -- Or see alternative below

COPY tmp_x FROM '/absolute/path/to/file' (FORMAT csv);

UPDATE tbl
SET banana = tmp_x.banana
FROM tmp_x
WHERE tbl.id = tmp_x.id;

DROP TABLE tmp_x; -- Alternatively, it's automatically dropped at the end of the session</code>

Pour une approche plus rationalisée lorsque la structure de la table temporaire reflète la table cible :

<code class="language-sql">CREATE TEMP TABLE tmp_x AS SELECT * FROM tbl LIMIT 0;</code>

Cela crée une table temporaire vide avec le même schéma que tbl, en omettant les contraintes.

Considérations relatives à la sécurité et aux performances

Avant Postgres 11, COPY nécessitait des privilèges de superutilisateur. Cependant, Postgres 11 et versions ultérieures proposent des rôles prédéfinis (comme pg_read_server_files et pg_write_server_files) pour une sécurité améliorée.

La psql méta-commande copy offre une autre solution, exécutant la commande COPY localement, contournant ainsi les exigences de privilèges de superutilisateur.

Pour les fichiers CSV volumineux, l'optimisation des performances est essentielle. Pensez à augmenter temporairement le paramètre temp_buffers. La création d'un index sur la colonne id de la table temporaire et l'exécution de ANALYZE peuvent encore améliorer la vitesse des requêtes.

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!