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

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

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

服务器端

如果您想要易于重用或自动化的东西，可以使用 Postgresql 的内置COPY命令。例如

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

此方法完全在远程服务器上运行 - 它无法写入本地 PC。它还需要作为 Postgres“超级用户”（通常称为 “root”）运行，因为 Postgres 无法阻止它使用该机器的本地文件系统进行令人讨厌的事情。

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

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

1. 应该允许用户在磁盘上读 / 写哪些文件 ？例如，这可能是一个特定的目录，文件名可能必须具有合适的前缀或扩展名。
2. 用户应该在数据库中读 / 写哪些表 ？这通常由GRANT在数据库中定义，但该函数现在以超级用户身份运行，因此通常 “超出界限” 的表将是完全可访问的。您可能不希望让某人调用您的函数并在 “用户” 表的末尾添加行...

我写了一篇扩展这种方法的博客文章 ，包括一些导出（或导入）符合严格条件的文件和表格的函数示例。

客户端

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

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

psql命令行客户端有一个特殊的 “元命令”，名为\copy ，它采用与 “真正的” COPY相同的选项，但是在客户端内部运行：

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

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

来自文档 ：

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

您的应用程序编程语言也可以对推或获取数据的支持，但你不能一般使用COPY FROM STDIN / TO STDOUT标准的 SQL 语句中，因为没有连接输入 / 输出流的方式。 PHP 的 PostgreSQL 处理程序（ 不是 PDO）包括非常基本的pg_copy_from和pg_copy_to函数，它们复制到 PHP 数组或从 PHP 数组复制，这对于大型数据集可能效率不高。

有几种解决方案：

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，但这不是可编写脚本的。

在终端（连接到 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