PostgreSQLのPL / pgSQL出力をCSVファイルに保存する



Answers

いくつかのソリューションがあります。

1 psqlコマンド

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

これは、 ssh postgres@host commandように、SSH経由で使うことができるという大きな利点があります。

2 postgres copyコマンド

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

3 psqlのインタラクティブ(または非)

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

それらはすべてスクリプトで使用できますが、#1が好きです。

4 pgadminしかし、それはスクリプト可能ではありません。

Question

PostgreSQLデータベースからCSVファイルにPL / pgSQL出力を保存する最も簡単な方法は何ですか?

私はPostgreSQL 8.4をpgAdmin IIIとPSQLプラグインで使用しています。ここで私はクエリを実行します。




私はCOPY TO機能をサポートしていないAWS Redshiftに取り組んでいます。

私のBIツールはタブ区切りのCSVをサポートしているので、以下を使用しました:

 psql -h  dblocation  -p port -U user  -d dbname  -F $'\t' --no-align -c " SELECT *   FROM TABLE" > outfile.csv



import json
cursor = conn.cursor()
qry = """ SELECT details FROM test_csvfile """ 
cursor.execute(qry)
rows = cursor.fetchall()

value = json.dumps(rows)

with open("/home/asha/Desktop/Income_output.json","w+") as f:
    f.write(value)
print 'Saved to File Successfully'



psqlはこれを行うことができます:

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:~$

ここで使用するオプションについては、 man psqlを参照してください。




より長いクエリがあり、psqlを使いたい場合は、クエリをファイルに置き、次のコマンドを使用します:

psql -d my_db_name -t -A -F";" -f input-file.sql -o output-file.csv



ヘッダーとともに特定のテーブルのすべての列に興味がある場合は、

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

これは、

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

私の知る限りでは同等です。




この情報は実際にはうまく表現されていません。 これを派生させる必要があるのは今回が2度目なので、私は何もしなければ自分自身に思い出させるためにここに入れます。

本当にこれを行う最良の方法(ポストグルからCSVを取得する)は、 COPY ... TO STDOUTコマンドを使用することCOPY ... TO STDOUT 。 あなたはそれをここの答えに示されているようにしたくないのですが。 このコマンドを使用する正しい方法は次のとおりです。

COPY (select id, name from groups) TO STDOUT WITH CSV HEADER

1つのコマンドだけを覚

ssh上での使用には最適です。

$ ssh psqlserver.example.com 'psql -d mydb "COPY (select id, name from groups) TO STDOUT WITH CSV HEADER"' > groups.csv

ssh上のドッカー内での使用には最適です。

$ ssh pgserver.example.com 'docker exec -tu postgres postgres psql -d mydb -c "COPY groups TO STDOUT WITH CSV HEADER"' > groups.csv

それはローカルマシン上ですばらしいです。

$ psql -d mydb -c 'COPY groups TO STDOUT WITH CSV HEADER' > groups.csv

ローカルマシンのドッカーの中に?:

docker exec -tu postgres postgres psql -d mydb -c 'COPY groups TO STDOUT WITH CSV HEADER' > groups.csv

またはkubernetesクラスター上で、ドッカーで、HTTPSを介して??:

kubectl exec -t postgres-2592991581-ws2td 'psql -d mydb -c "COPY groups TO STDOUT WITH CSV HEADER"' > groups.csv

とても汎用性があり、多くのカンマ!

あなたもですか?

はい、私はしました、ここに私のメモがあります:

COPYses

/copyを使用すると、 psqlコマンドが実行されているシステム上でファイル操作が効果的に実行されます。 リモートサーバに接続する場合、 psqlを実行しているシステム上のデータファイルをリモートサーバにコピーするのは簡単です。

COPYは、バックエンドプロセスのユーザーアカウント(デフォルトのpostgres )としてサーバー上でファイル操作を実行し、ファイルパスとアクセス許可が適切にチェックされて適用されます。 TO STDOUTを使用すると、ファイル許可チェックがバイパスされます。

結果のCSVを最終的に存在させたいシステム上でpsqlが実行されていない場合、これらのオプションはどちらも後続のファイル移動を必要とします。 私が経験したことは、主にリモートサーバーで作業する場合、これが最も可能性の高いケースです。

シンプルなCSV出力のために、ssh経由でTCP / IPトンネルのようなものをリモートシステムに設定するのはもっと複雑ですが、他の出力形式(バイナリ)では、トンネル接続での/copyやローカルpsql実行が良いかもしれません。 同様に、大量のインポートでは、ソースファイルをサーバーに移動し、 COPYを使用するのがおそらく最高性能のオプションです。

PSQLのパラメータ

psqlパラメータを使用すると、CSVのような出力をフォーマットできますが、ページャを無効にしてヘッダーを取得しないように覚えておく必要があるという欠点があります。

$ psql -P pager=off -d mydb -t -A -F',' -c 'select * from groups;'
2,Technician,Test 2,,,t,,0,,                                                                                                                                                                   
3,Truck,1,2017-10-02,,t,,0,,                                                                                                                                                                   
4,Truck,2,2017-10-02,,t,,0,,

その他のツール

いいえ、ちょうどツールをコンパイルしたりインストールしたりせずにサーバーからCSVを取り出したいだけです。




Related