将 PostgreSQL 的 PL / pgSQL 输出保存到 CSV 文件

将 PL / pgSQL 输出从 PostgreSQL 数据库保存到 CSV 文件的最简单方法是什么?

我将 PostgreSQL 8.4 与 pgAdmin III 和 PSQL 插件一起使用,从中运行查询。

答案

Copy (Select * From foo) To '/tmp/test.csv' With CSV DELIMITER ',';
\copy (Select * From foo) To '/tmp/test.csv' With CSV
>psql dbname
psql>\f ','
psql>\a
psql>\o '/tmp/output.csv'
psql>SELECT * from users;
psql>\q
\f ','
\a
\t
\o '/tmp/yourOutputFile.csv'
:select * from YOUR_TABLE
\o
cd /tmp
nano /tmp/yourOutputFile.csv
COPY table TO '/some_destdir/mycsv.csv' WITH CSV HEADER;
COPY (SELECT * FROM table) TO '/some_destdir/mycsv.csv' WITH CSV HEADER;
COPY (select id, name from groups) TO STDOUT WITH CSV HEADER
$ ssh psqlserver.example.com 'psql -d mydb "COPY (select id, name from groups) TO STDOUT WITH CSV HEADER"' > groups.csv
$ 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
kubectl exec -t postgres-2592991581-ws2td 'psql -d mydb -c "COPY groups TO STDOUT WITH CSV HEADER"' > groups.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,,
ERROR:  could not open file "/filepath/places.csv" for writing: Permission denied
\Copy (Select address, zip  From manjadata) To '/filepath/places.csv' With CSV;
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:~$
psql -h dblocation -p port -U user -d dbname -F $'\t' --no-align -c "SELECT * FROM TABLE" > outfile.csv
psql -c "SELECT * FROM pg_catalog.pg_tables" --csv  postgres

psql -c "SELECT * FROM pg_catalog.pg_tables" --csv -P csv_fieldsep='^'  postgres

psql -c "SELECT * FROM pg_catalog.pg_tables" --csv  postgres > output.csv

在 pgAdmin III 中,有一个选项可以从查询窗口导出到文件。在主菜单中,它是 “查询”->“执行到文件”,或者有一个按钮执行相同的操作(这是一个带有蓝色软盘的绿色三角形,而不是只运行查询的普通绿色三角形)。如果您不是从查询窗口运行查询,那么我将按照 IMSoP 的建议进行操作,并使用 copy 命令。