Introduction à COPY
Dans PostgreSQL, la commande SQL COPY est utilisée pour faire des duplications de tables, d’enregistrements et d’autres objets, mais elle est également utile pour transférer des données d’un format à un autre. Par exemple, la commande COPY peut être utilisée pour insérer des données CSV dans une table sous forme d’enregistrements PostgreSQL. Cet article fournira plusieurs exemples de COPIE PostgreSQL qui illustrent comment utiliser cette commande dans le cadre de l’administration de votre base de données.
L’instruction PostgreSQL COPY
L’instruction SQL COPY est similaire aux commandes pg_dump et pg_restore qui sont utilisées pour sauvegarder et restaurer des tables et des bases de données. Vous pouvez utiliser la commande COPY pour déplacer des fichiers dans une table PostgreSQL ou pour mettre les données d’une table PostgreSQL dans un fichier.
Prérequis
Avant de commencer à regarder nos exemples de COPIE PostgreSQL, nous devrons revoir certains des prérequis de ce tutoriel :
- Premièrement, vous devrez avoir PostgreSQL installé et en fonctionnement sur votre machine. Si vous utilisez Linux, essayez la commande
service postgresql statusdans un terminal pour voir si PostgreSQL est installé et fonctionne. Vous pouvez ensuite utiliser CTRL + C pour quitter le processus. Sur macOS ou Linux, vous pouvez également faire passer la commandegrepparpspour rechercher tout processus PostgreSQL en cours d’exécution :
|
1
|
ps aux | grep postgres
|
Vous pouvez également utiliser psql -V pour confirmer que l’interface en ligne de commande psqlline interface est installée et fonctionne ; cette commande renvoie les informations sur la version et l’installation de l’utilitaire.
Vous devriez voir une sortie qui ressemble à ce qui suit :
|
1
|
psql (PostgreSQL) 11.5
|
Connexion à l’interface de ligne de commande psql pour PostgreSQL
Une fois que tous les prérequis sont en place, vous pouvez essayer de vous connecter à l’interface psql. Utilisez le sudo su - postgres dans un terminal suivi de psql. La commande psql --help peut être utilisée pour obtenir plus d’informations sur la connexion à une base de données PostgreSQL avec un utilisateur.
Créer une base de données PostgreSQL
Voici la commande que nous utiliserons dans l’interface psql pour créer une base de données PostgreSQL :
|
1
|
CREATE DATABASE somedb ;
|
Maintenant, nous pouvons nous connecter à notre base de données à l’aide de la commande \c afin de commencer à saisir des requêtes.
Créer une table PostgreSQL
Puis, nous allons créer une table à utiliser dans nos exemples :
|
1
2 3 4 5 |
CREATE TABLE csv_tbl(
id SERIAL PRIMARY KEY, prénom TEXTE, nom de famille TEXTE ) ; |
Exemples de copie PostgreSQL
À ce stade, nous sommes prêts à plonger dans quelques exemples de la façon dont nous pouvons utiliser la commande SQL COPY pour dupliquer ou transférer des données.
Copier les données d’un fichier texte dans Postgres
Un format de fichier TEXT est constitué de lignes composées de colonnes qui sont séparées par un caractère de délimitation ; les données sont alors écrites ou lues comme un fichier texte avec une ligne d’un tableau par ligne.
|
1
2 |
COPY TABLE_NAME (col, col2)
FROM / TO ‘filename’ DELIMITER ‘ ‘ ; |
Nous pouvons utiliser la commande COPY de PostgreSQL pour exporter un fichier CSV dans notre table de démonstration avec un format de fichier TEXT :
|
1
2 |
COPY demo(firstname, lastname) TO
‘demo.txt’ DELIMITER ‘ ‘ ; |
Copier des données binaires dans Postgres
Un BINARY format de fichier est identifié comme un fichier ‘non-textuel’ puisque toutes les données sont stockées ou lues au format binaire. Cela peut être plus rapide que le format texte et csv.
Essayons d’utiliser la commande COPY pour exporter un seul enregistrement d’un tableau dans un fichier binaire. Voici la syntaxe de base :
|
1
2 3 4 5 6 |
COPY (
SELECT col1, col2, col3 FROM TABLE_NAME WHERE {condition} ) TO STDOUT WITH BINARY ; |
Pour notre exemple, la commande ressemblera à ceci :
|
1
2 3 4 5 |
COPY (
SELECT firstname, lastname FROM demo WHERE id=50 ) TO STDOUT WITH BINARY ; PGCOPY |
Insérer des données CSV dans PostgreSQL en utilisant COPY
Maintenant que nous avons examiné quelques exemples d’exportation de données, essayons d’utiliser COPY pour insérer des données de lignes CSV dans une table PostgreSQL. Nous utiliserons les deux lignes suivantes de données de test CSV pour illustrer comment vous pouvez utiliser COPY pour insérer des données dans Postgres à partir d’un fichier CSV :
|
1
2 3 |
id, str_col, int_col, bool_col
1, « je viens d’un fichier CSV », 112233, true 2, « bonjour, Postgres », 8765, false |
Terminologie CSV
Lorsque nous parlons de fichiers CSV, il y a quelques termes clés à comprendre :
-
DELIMITER– Un délimiteur est un caractère qui sépare chaque ligne du fichier en colonnes ; dans un fichier CSV, le délimiteur est une virgule. Les délimiteurs ne peuvent pas être utilisés au format binaire. -
HEADER– Lorsqu’un fichier CSV est créé, la ligne d’en-tête est la première ligne du fichier qui contient les noms des colonnes.
Utiliser les mots-clés COPY et FROM pour insérer des données Postgres à partir d’un fichier
L’instruction SQL suivante insère des lignes de données CSV dans une table appelée some_table :
|
1
2 3 |
COPY some_table
FROM ‘/Users/macuser/postgres-data.csv’ DELIMITER ‘,’ CSV HEADER; |
NOTE : Assurez-vous de passer le chemin absolu du fichier comme une chaîne de caractères dans votre FROM clause pour éviter toute erreur.
Utiliser COPY pour couper les espaces blancs des données de la table PostgreSQL
Un problème mineur lié à l’utilisation de COPY pour insérer des données CSV est qu’il en résultera des espaces blancs en tête et en queue dans notre colonne de chaîne. Heureusement, il existe une solution simple à ce problème. Essayons à nouveau d’insérer nos données CSV, mais cette fois, nous utiliserons une table temporaire pour stocker les données. Nous utiliserons ensuite la fonction LTRIM de SQL pour supprimer les espaces blancs de notre colonne de chaînes de caractères avant de l’insérer dans notre table PostgreSQL permanente.
NOTE : Assurez-vous de tronquer la table de test en tapant TRUNCATE some_table; dans psql avant de réinsérer les données CSV afin d’éviter toute erreur de clé dupliquée.
Créer une table temporaire pour stocker les données CSV
L’instruction SQL suivante va créer une table temporaire pour nos données CSV :
|
1
2 |
CREATE TEMP TABLE temp_tbl AS
SELECT * FROM some_table LIMIT 0 ; |
Cette commande SQL retournera SELECT 0 en cas de succès.
Maintenant, essayons à nouveau la commande COPY, sauf que cette fois, nous allons copier les lignes du CSV dans la table temporaire :
|
1
2 3 |
COPY temp_tbl
FROM ‘/Users/macuser/postgres-data.csv’ DELIMITER ‘,’ CSV HEADER; |
L’instruction SQL présentée ci-dessus devrait retourner COPY suivie du nombre de lignes CSV qu’elle a copiées.
Utiliser LTRIM() pour retirer les espaces blancs des données de la table PostgreSQL
Notre prochain exemple va insérer les données de la table temporaire dans la table permanente. Lorsque nous exécutons cette instruction INSERT, nous devons spécifier explicitement chaque en-tête de colonne et nous assurer de passer la colonne string à la fonction LTRIM() afin de dépouiller les espaces blancs :
|
1
2 3 4 |
INSERT INTO some_table
(id, str_col, int_col, bool_col) SELECT id, LTRIM(str_col), int_col, bool_col FROM temp_tbl ; |
Après avoir exécuté cette INSERT commande, les enregistrements devraient être copiés de la table temporaire dans notre some_table table PostgreSQL. Maintenant, nous allons utiliser l’instruction SELECT * FROM some_table pour vérifier que les données CSV ont été insérées sans les espaces blancs de début et de fin.
La commande présentée ci-dessous n’est nécessaire que si vous devez libérer de la mémoire ou de l’espace à court terme ; sinon, la table temporaire sera abandonnée automatiquement lorsque vous terminerez votre session psql :
|
1
|
DROP TABLE temp_tbl ;
|
Conclusion
Il ne fait aucun doute que la commande SQL COPY peut être utilisée pour une variété de tâches d’administration de bases de données. Non seulement vous pouvez utiliser la commande pour dupliquer des tables et des enregistrements, mais vous pouvez également utiliser COPY pour transférer des données d’un format à un autre. Dans cet article, nous vous avons montré des exemples de PostgreSQL COPY qui illustrent comment à la fois importer et exporter des données entre un fichier CSV et une table PostgreSQL. Avec ces exemples pour commencer, vous serez en mesure d’exploiter la puissance de la commande COPY dans votre propre implémentation PostgreSQL.
0 commentaire