将PostgreSQL的PL / pgSQL输出保存为CSV文件-Java 学习之路

731

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

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

16 回答

5
我正在使用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
```
回复于 2024-05-03T02:25:51+08:00
1105
我尝试了几件事，但很少有人能够通过 Headers 详细信息向我提供所需的CSV .

这对我有用 .
```
psql -d dbame -U username \
  -c "COPY ( SELECT * FROM TABLE ) TO STDOUT WITH CSV HEADER " > \
  OUTPUT_CSV_FILE.csv
```
回复于 2024-05-03T02:25:51+08:00

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 .

回复于 2024-05-03T02:25:51+08:00

16
如果您对特定表的所有列以及 Headers 感兴趣，则可以使用
```
COPY table TO '/some_destdir/mycsv.csv' WITH CSV HEADER;
```
这比一点点简单
```
COPY (SELECT * FROM table) TO '/some_destdir/mycsv.csv' WITH CSV HEADER;
```
据我所知，这是等价的 .
回复于 2024-05-03T02:25:51+08:00
432
您想要在服务器上还是在客户端上生成文件？

服务器端

如果您想要一些易于重用或自动化的东西，可以使用Postgresql内置的COPY命令 . 例如
```
Copy (Select * From foo) To '/tmp/test.csv' With CSV DELIMITER ',';
```
This approach runs entirely on the remote server - 它可以阻止它使用该机器的本地文件系统进行令人讨厌的事情 .

这实际上并不意味着您必须以超级用户身份进行连接（自动化会产生不同类型的安全风险），因为您可以使用the SECURITY DEFINER option to CREATE FUNCTION创建一个像超级用户一样运行的功能 .

关键部分是你的功能是执行额外的检查，而不仅仅是绕过安全 - 所以你可以写一个导出你需要的确切数据的函数，或者你可以写一些可以接受各种选项的东西，只要它们遇到严格的白名单 . 你需要检查两件事：
- 允许用户在磁盘上读/写哪个 files ？例如，这可能是一个特定的目录，文件名可能必须具有合适的前缀或扩展名 .
- 用户应该能够在数据库中读/写哪些 tables ？这通常由数据库中的 GRANT 定义，但该函数现在以超级用户身份运行，因此通常为"out of bounds"的表将是完全可访问的 . 您可能不希望让某人调用您的函数并在“用户”表的末尾添加行...
我写了a blog post expanding on this approach，包括一些导出（或导入）符合严格条件的文件和表的函数示例 .

客户端

另一种方法是 do the file handling on the client side ，即在您的应用程序或脚本中 . Postgres服务器没有't need to know what file you'重新复制到它，它只是吐出数据而客户端把它放在某个地方 .

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

psql command-line client 有一个特殊的"meta-command"，名为 \copy ，它采用与"real" COPY 相同的选项，但在客户端内部运行：
```
\copy (Select * From foo) To '/tmp/test.csv' With CSV
```
请注意，没有终止 ; ，因为与SQL命令不同，元命令由换行符终止 .

从the docs：

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

您的应用程序编程语言也可能支持推送或获取数据，但您通常不能在标准SQL语句中使用 COPY FROM STDIN / TO STDOUT ，因为无法连接输入/输出流 . PHP的PostgreSQL处理程序（不是PDO）包括非常基本的pg_copy_from和pg_copy_to函数，它们复制到PHP数组或从PHP数组复制，这对于大型数据集可能效率不高 .
回复于 2024-05-03T02:25:51+08:00

我不得不使用\ 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;

它正在发挥作用

回复于 2024-05-03T02:25:51+08:00

11
要使用列名作为HEADER下载CSV文件，请使用以下命令：
```
Copy (Select * From tableName) To '/tmp/fileName.csv' With CSV HEADER;
```
回复于 2024-05-03T02:25:51+08:00
10

JackDB，Web浏览器中的数据库客户端，使这非常简单 . 特别是如果你在Heroku上 .

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

来源jackdb-heroku http://static.jackdb.com/assets/img/blog/jackdb-heroku-oauth-connect.gif

连接数据库后，您可以运行查询并导出为CSV或TXT（请参见右下角） .

注意：我与JackDB无关 . 我目前使用他们的免费服务，并认为它是一个伟大的产品 .

回复于 2024-05-03T02:25:51+08:00
13
新版本 - psql 12 - 将支持 --csv .

psql - devel --csv切换到CSV（逗号分隔值）输出模式 . 这相当于\ pset format csv . csv_fieldsep指定要以CSV输出格式使用的字段分隔符 . 如果分隔符字符出现在字段的值中，则该字段将按照双引号输出，遵循标准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
```
回复于 2024-05-03T02:25:51+08:00
20
在终端（连接到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
```
回复于 2024-05-03T02:25:51+08:00
0
如果您有更长的查询并且您想使用psql，则将查询放入文件并使用以下命令：
```
psql -d my_db_name -t -A -F";" -f input-file.sql -o output-file.csv
```
回复于 2024-05-03T02:25:51+08:00
6
有几种解决方案：

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交互式（或不是）
```
>psql dbname
psql>\f ','
psql>\a
psql>\o '/tmp/output.csv'
psql>SELECT * from users;
psql>\q
```
所有这些都可以在脚本中使用，但我更喜欢＃1 .

4 pgadmin但这不是可编写脚本的 .
回复于 2024-05-03T02:25:51+08:00

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'

回复于 2024-05-03T02:25:51+08:00

1
CSV导出统一

这些信息并没有得到很好的体现 . 因为这是我第二次需要得到这个，我会把它放在这里提醒自己，如果没有别的 .

真的最好的方法（从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 的系统上的数据文件复制到远程服务器或从远程服务器复制数据文件 .

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

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

将ssh上的TCP / IP隧道配置为远程系统以进行简单的CSV输出更为复杂，但对于其他输出格式（二进制），通过隧道连接 /copy 可能更好，执行本地 psql . 与此类似，对于大型导入，将源文件移动到服务器并使用 COPY 可能是性能最高的选项 .

PSQL参数

使用psql参数，您可以像CSV一样格式化输出，但有一些缺点，例如必须记住禁用寻呼机而不是获取 Headers ：
```
$ 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从我的服务器中取出 .
回复于 2024-05-03T02:25:51+08:00
79

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

回复于 2024-05-03T02:25:51+08:00
-3
我写了一个名为psql2csv的小工具，它封装了 COPY query TO STDOUT 模式，从而产生了正确的CSV . 它的界面类似于 psql .
```
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
```
回复于 2024-05-03T02:25:51+08:00

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

16 回答

服务器端

客户端

1 psql命令

2 postgres copy命令

3 psql交互式（或不是）

4 pgadmin但这不是可编写脚本的 .

CSV导出统一

记住一个命令！

你甚至？

COPYses

PSQL参数

其他工具

相关问题