Question Enregistrer la sortie PL / pgSQL de PostgreSQL dans un fichier CSV


Quel est le moyen le plus simple d'enregistrer la sortie PL / pgSQL d'une base de données PostgreSQL dans un fichier CSV?

J'utilise PostgreSQL 8.4 avec pgAdmin III et le plugin PSQL où j'exécute des requêtes.


686
2017-10-04 22:58


origine


Réponses:


Voulez-vous le fichier résultant sur le serveur ou sur le client?

Du côté serveur

Si vous voulez quelque chose de facile à réutiliser ou automatiser, vous pouvez utiliser Postgresql intégré dans COPIE commander. par exemple.

Copy (Select * From foo) To '/tmp/test.csv' With CSV DELIMITER ',';

Cette approche s'exécute entièrement sur le serveur distant - Il ne peut pas écrire sur votre PC local. Il doit également être exécuté en tant que superutilisateur Postgres (normalement appelé "root") car Postgres ne peut pas l'empêcher de faire des choses désagréables avec le système de fichiers local de cette machine.

Cela ne signifie pas que vous devez être connecté en tant que superutilisateur (l'automatisation de ce risque de sécurité serait différente), car vous pouvez utiliser la SECURITY DEFINER option à CREATE FUNCTION pour faire une fonction qui fonctionne comme si vous étiez un super-utilisateur.

La partie cruciale est que votre fonction est là pour effectuer des vérifications supplémentaires, pas seulement pour contourner la sécurité. Vous pouvez donc écrire une fonction qui exporte les données exactes dont vous avez besoin, ou vous pouvez écrire quelque chose qui accepte diverses options. répondre à une liste blanche stricte. Vous devez vérifier deux choses:

  1. Lequel des dossiers L'utilisateur devrait-il être autorisé à lire / écrire sur le disque? Cela peut être un répertoire particulier, par exemple, et le nom de fichier peut avoir un préfixe ou une extension approprié.
  2. Lequel les tables L'utilisateur devrait-il être capable de lire / écrire dans la base de données? Ce serait normalement défini par GRANTs dans la base de données, mais la fonction s'exécute maintenant en tant que superutilisateur, de sorte que les tables qui seraient normalement "hors limites" seront entièrement accessibles. Vous ne voulez probablement pas laisser quelqu'un invoquer votre fonction et ajouter des lignes à la fin de votre table "users" ...

j'ai écris un article de blog développant cette approche, y compris quelques exemples de fonctions qui exportent (ou importent) des fichiers et des tables répondant à des conditions strictes.


Côté client

L'autre approche consiste à faire le traitement de fichiers du côté client, c'est-à-dire dans votre application ou script. Le serveur Postgres n'a pas besoin de savoir à quel fichier vous copiez, il crache simplement les données et le client le place quelque part.

La syntaxe sous-jacente à ceci est la COPY TO STDOUT commande, et des outils graphiques comme pgAdmin l'emballeront pour vous dans une belle boîte de dialogue.

le psql client de ligne de commande a une "méta-commande" spéciale appelée \copy, qui prend toutes les mêmes options que le "vrai" COPY, mais est exécuté à l'intérieur du client:

\copy (Select * From foo) To '/tmp/test.csv' With CSV

Notez qu'il n'y a pas de terminaison ;, car les méta-commandes sont terminées par newline, contrairement aux commandes SQL.

De les docs:

Ne confondez pas COPY avec l'instruction psql \ copy. \ copy invoque COPY FROM STDIN ou COPY TO STDOUT, puis extrait / stocke les données dans un fichier accessible au client psql. Ainsi, l'accessibilité aux fichiers et les droits d'accès dépendent du client plutôt que du serveur lorsque \ copy est utilisé.

Votre langage de programmation d'application mai ont également un support pour pousser ou récupérer les données, mais vous ne pouvez généralement pas utiliser COPY FROM STDIN/TO STDOUT dans une instruction SQL standard, car il n'y a aucun moyen de connecter le flux d'entrée / sortie. Le gestionnaire PostgreSQL de PHP (ne pas PDO) comprend très basique pg_copy_from et pg_copy_to fonctions qui copient vers / depuis un tableau PHP, ce qui peut ne pas être efficace pour les grands ensembles de données.


1031
2017-10-04 23:18



Il y a plusieurs solutions:

1 psql commander

psql -d dbname -t -A -F"," -c "select * from users" > output.csv

Cela a le grand avantage que vous pouvez l'utiliser via SSH, comme ssh postgres@host command - vous permettant d'obtenir

2 postgres copy commander

COPY (SELECT * from users) To '/tmp/output.csv' With CSV;

3 psql interactif (ou non)

>psql dbname
psql>\f ','
psql>\a
psql>\o '/tmp/output.csv'
psql>SELECT * from users;
psql>\q

Tous peuvent être utilisés dans les scripts, mais je préfère # 1.

4 pgadmin mais ce n'est pas scriptable.


410
2017-08-08 17:56



Dans le terminal (tout en étant connecté à la base de données), définissez la sortie dans le fichier cvs

1) Réglez le séparateur de champ sur ',':

\f ','

2) Définir le format de sortie non aligné:

\a

3) Afficher uniquement les tuples:

\t

4) Définissez la sortie:

\o '/tmp/yourOutputFile.csv'

5) Exécutez votre requête:

:select * from YOUR_TABLE

6) Sortie:

\o

Vous pourrez ensuite trouver votre fichier csv à cet emplacement:

cd /tmp

Copiez-le en utilisant le scp commande ou éditer en utilisant nano:

nano /tmp/yourOutputFile.csv

75
2018-06-11 11:18



Si vous êtes intéressé par tout les colonnes d'une table particulière avec les en-têtes, vous pouvez utiliser

COPY table TO '/some_destdir/mycsv.csv' WITH CSV HEADER;

C'est un peu plus simple que

COPY (SELECT * FROM table) TO '/some_destdir/mycsv.csv' WITH CSV HEADER;

qui, à ma connaissance, sont équivalents.


32
2018-01-11 20:34



J'ai dû utiliser le \ COPY parce que j'ai reçu le message d'erreur:

ERROR:  could not open file "/filepath/places.csv" for writing: Permission denied

J'ai donc utilisé:

\Copy (Select address, zip  From manjadata) To '/filepath/places.csv' With CSV;

et ça fonctionne


20
2018-06-28 21:41



psql peut le faire pour vous:

edd@ron:~$ psql -d beancounter -t -A -F"," \
                -c "select date, symbol, day_close " \
                   "from stockprices where symbol like 'I%' " \
                   "and date >= '2009-10-02'"
2009-10-02,IBM,119.02
2009-10-02,IEF,92.77
2009-10-02,IEV,37.05
2009-10-02,IJH,66.18
2009-10-02,IJR,50.33
2009-10-02,ILF,42.24
2009-10-02,INTC,18.97
2009-10-02,IP,21.39
edd@ron:~$

Voir man psql pour de l'aide sur les options utilisées ici.


14
2017-10-04 23:12



Dans pgAdmin III, il y a une option pour exporter vers un fichier à partir de la fenêtre de requête. Dans le menu principal, c'est Query -> Execute to file ou il y a un bouton qui fait la même chose (c'est un triangle vert avec une disquette bleue par opposition au triangle vert qui lance la requête). Si vous n'exécutez pas la requête depuis la fenêtre de requête, je ferais ce que IMSoP a suggéré et j'utiliserais la commande copy.


11
2017-11-04 16:58