提问



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


我正在使用PostgreSQL 8.4和pgAdmin III以及我运行查询的PSQL插件。

最佳参考


您想要在服务器上还是在客户端上生成文件?


服务器端



如果你想要一些易于重用或自动化的东西,你可以使用Postgresql内置的COPY命令。例如[65]


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


这种方法完全在远程服务器上运行 - 它无法写入本地PC。它还需要作为Postgres超级用户(通常称为root)运行,因为Postgres不能阻止它使用该机器的本地文件系统做令人讨厌的事情。


这实际上并不意味着你必须以超级用户身份连接(自动化会产生不同类型的安全风险),因为你可以使用CREATE FUNCTION选项来CREATE FUNCTION来创建一个函数就像你是超级用户一样运行。[66]


关键部分是你的功能是执行额外的检查,而不仅仅是绕过安全 - 所以你可以写一个导出你需要的确切数据的函数,或者你可以写一些可以接受各种选项的东西,只要它们遇到严格的白名单。你需要检查两件事:



  1. 允许用户在磁盘上读/写哪个文件?例如,这可能是一个特定的目录,文件名可能必须具有合适的前缀或扩展名。

  2. 用户应该能够在数据库中读/写哪些?这通常由数据库中的GRANT定义,但该函数现在作为超级用户运行,因此通常超出界限的表将是完全可访问的。您可能不希望让某人调用您的函数并在用户表的末尾添加行...



我已经写了一篇扩展这种方法的博客文章,包括一些导出(或导入)符合严格条件的文件和表格的函数示例。[67]





客户端



另一种方法是在客户端进行文件处理,即在您的应用程序或脚本中。 Postgres服务器不需要知道你要复制到哪个文件,它只是吐出数据而客户端把它放在某个地方。


这个的基本语法是COPY TO STDOUT命令,像pgAdmin这样的图形工具会在一个很好的对话框中为你包装它。


psql命令行客户端有一个特殊的元命令,名为 \copy ,它采用与真实相同的选项COPY,但是在客户端内运行:


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


请注意,没有终止;,因为与SQL命令不同,元命令由换行符终止。


来自文档:[68]



  不要将COPY与psql指令\\ copy混淆。 \\ copy调用COPY FROM STDIN或COPY TO STDOUT,然后将数据提取/存储在psql客户端可访问的文件中。因此,使用\\ copy时,文件可访问性和访问权限取决于客户端而不是服务器。



您的应用程序编程语言可能也支持推送或获取数据,但您通常不能在标准SQL语句中使用COPY FROM STDIN/TO STDOUT,因为没有办法连接输入/输出流。 PHP的PostgreSQL处理程序(不是 PDO)包括非常基本的pg_copy_frompg_copy_to函数,这些函数复制到PHP数组或从PHP数组复制,这对于大型数据集可能效率不高。 [69] [70]

其它参考1


有几种解决方案:


1 psql命令



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


这有一个很大的优势,你可以通过SSH使用它,如ssh postgres@host command - 让你得到


2 postgres copy命令



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


3 psql interactive(或不是)



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


所有这些都可以在脚本中使用,但我更喜欢#1。


4 pgadmin,但那不是可编写脚本的。


其它参考2


在终端(连接到db时)将输出设置为cvs文件


1)将字段分隔符设置为',':


\f ','


2)设置输出格式不对齐:


\a


3)仅显示元组:


\t


4)设置输出:


\o '/tmp/yourOutputFile.csv'


5)执行您的查询:


:select * from YOUR_TABLE


6)输出:


\o


然后,您就可以在此位置找到您的csv文件:


cd /tmp


使用scp命令复制或使用nano编辑:


nano /tmp/yourOutputFile.csv

其它参考3


如果您对所有特定表的列以及标题感兴趣,则可以使用


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


这比一点点简单


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


据我所知,这是等价的。

其它参考4


我不得不使用\\ COPY,因为我收到了错误消息:


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


所以我用过:


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


它正在发挥作用

其它参考5


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

其它参考6


在pgAdmin III中,有一个从查询窗口导出到文件的选项。在主菜单中,它的查询 - >执行文件或sa按钮执行相同的操作(它是一个带有蓝色软盘的绿色三角形,而不是只运行查询的普通绿色三角形)。如果你我没有从查询窗口运行查询,而是执行IMSoP建议并使用复制命令。

其它参考7


我正在使用AWS Redshift,它不支持COPY TO功能。


我的BI工具虽然支持制表符分隔的CSV,所以我使用了以下内容:


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

其它参考8


这个信息并没有得到很好的体现。因为这是我第二次需要得出这个信息,所以我会把它放在这里提醒自己,如果没有其他的话。


真的最好的方法(从postgres中获取CSV)是使用COPY ... TO STDOUT命令。虽然你不想按照这里的答案所示的方式去做。使用命令的正确方法是:


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



  记住一个命令!



它非常适合在ssh上使用:


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


它非常适合在ssh中使用docker:


$ 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里面?:


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


或者在kubernetes集群上,在docker中,通过HTTPS ??:


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


多功能,多逗号!


你甚至?



是的,我做了,这是我的笔记:


COPYses



使用/copy可以在运行psql命令的任何系统上有效地执行文件操作,作为执行它的用户1。如果连接到远程服务器,将执行psql的系统上的数据文件复制到远程服务器或从远程服务器复制数据文件很简单。[71]


COPY作为后端进程用户帐户(默认postgres)在服务器上执行文件操作,检查并相应地应用文件路径和权限。如果使用TO STDOUT,则会绕过文件权限检查。


如果psql未在您希望最终驻留的结果CSV的系统上执行,则这两个选项都需要后续文件移动。根据我的经验,当您主要使用远程服务器时,这是最可能的情况。


将ssh上的TCP/IP隧道配置为远程系统以进行简单的CSV输出会更复杂,但对于其他输出格式(二进制),通过隧道连接[[执行本地操作]] /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从我的服务器中取出。

其它参考9


我写了一个名为psql2csv的小工具,它封装了COPY query TO STDOUT模式,产生了正确的CSV。它的界面类似于psql[72]


psql2csv [OPTIONS] < QUERY
psql2csv [OPTIONS] QUERY


假定查询是STDIN的内容(如果存在)或最后一个参数。所有其他参数都转发到psql,除了这些:


-h, --help           show help, then exit
--encoding=ENCODING  use a different encoding than UTF8 (Excel likes LATIN1)
--no-header          do not output a header

其它参考10


如果您有更长的查询并且您想使用psql,则将查询放入文件并使用以下命令:


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

其它参考11


JackDB是Web浏览器中的数据库客户端,它使这非常简单。特别是如果你依赖Heroku。[73]


它允许您连接到远程数据库并对它们运行SQL查询。


&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP; &NBSP; 来源
jackdb-heroku http://static.jackdb.com/assets/img/blog/jackdb-heroku-oauth-connect.gif [75]





连接数据库后,您可以运行查询并导出为CSV或TXT(请参见右下角)。








注意:我与JackDB没有任何关系。我目前使用他们的免费服务并认为它是一个很棒的产品。

其它参考12


我尝试了几件事,但很少有人能够给我带有标题详细信息的所需csv。


这是我的工作。


psql -d dbame -U username -cCOPY(SELECT * FROM TABLE)TO STDOUT WITH CSV HEADER> OUTPUT_CSV_FILE.csv

其它参考13


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'