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

声明:本页面是StackOverFlow热门问题的中英对照翻译,遵循CC BY-SA 4.0协议,如果您需要使用它,必须同样遵循CC BY-SA许可,注明原文地址和作者信息,同时你必须将它归于原作者(不是我):StackOverFlow 原文地址: http://stackoverflow.com/questions/1517635/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me): StackOverFlow

提示:将鼠标放在中文语句上可以显示对应的英文。显示中英文
时间:2020-09-01 03:52:42  来源:igfitidea点击:

Save PL/pgSQL output from PostgreSQL to a CSV file

sqlpostgresqlcsvpostgresql-copy

提问by Hoff

What is the easiest way to save PL/pgSQL output from a PostgreSQL database to a CSV file?

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

I'm using PostgreSQL 8.4 with pgAdmin III and PSQL plugin where I run queries from.

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

回答by IMSoP

Do you want the resulting file on the server, or on the client?

您想要在服务器上还是在客户端上的结果文件?

Server side

服务器端

If you want something easy to re-use or automate, you can use Postgresql's built in COPYcommand. e.g.

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

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

This approach runs entirely on the remote server- it can't write to your local PC. It also needs to be run as a Postgres "superuser" (normally called "root") because Postgres can't stop it doing nasty things with that machine's local filesystem.

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

That doesn't actually mean you have to be connected as a superuser (automating that would be a security risk of a different kind), because you can use the SECURITY DEFINERoption to CREATE FUNCTIONto make a function which runs as though you were a superuser.

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

The crucial part is that your function is there to perform additional checks, not just by-pass the security - so you could write a function which exports the exact data you need, or you could write something which can accept various options as long as they meet a strict whitelist. You need to check two things:

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

  1. Which filesshould the user be allowed to read/write on disk? This might be a particular directory, for instance, and the filename might have to have a suitable prefix or extension.
  2. Which tablesshould the user be able to read/write in the database? This would normally be defined by GRANTs in the database, but the function is now running as a superuser, so tables which would normally be "out of bounds" will be fully accessible. You probably don't want to let someone invoke your function and add rows on the end of your “users” table…
  1. 应该允许用户在磁盘上读/写哪些文件?例如,这可能是一个特定的目录,并且文件名可能必须具有合适的前缀或扩展名。
  2. 用户应该能够在数据库中读/写哪些?这通常由GRANT数据库中的s定义,但该函数现在以超级用户身份运行,因此通常“越界”的表将完全可以访问。您可能不想让某人调用您的函数并在“用户”表的末尾添加行……

I've written a blog post expanding on this approach, including some examples of functions that export (or import) files and tables meeting strict conditions.

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



Client side

客户端

The other approach is to do the file handling on the client side, i.e. in your application or script. The Postgres server doesn't need to know what file you're copying to, it just spits out the data and the client puts it somewhere.

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

The underlying syntax for this is the COPY TO STDOUTcommand, and graphical tools like pgAdmin will wrap it for you in a nice dialog.

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

The psqlcommand-line clienthas a special "meta-command" called \copy, which takes all the same options as the "real" COPY, but is run inside the client:

psql命令行客户端有一个特殊的“元命令”之称\copy,这需要所有相同的选项,“真正的” COPY,但在运行客户端中:

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

Note that there is no terminating ;, because meta-commands are terminated by newline, unlike SQL commands.

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

From the docs:

文档

Do not confuse COPY with the psql instruction \copy. \copy invokes COPY FROM STDIN or COPY TO STDOUT, and then fetches/stores the data in a file accessible to the psql client. Thus, file accessibility and access rights depend on the client rather than the server when \copy is used.

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

Your application programming language mayalso have support for pushing or fetching the data, but you cannot generally use COPY FROM STDIN/TO STDOUTwithin a standard SQL statement, because there is no way of connecting the input/output stream. PHP's PostgreSQL handler (notPDO) includes very basic pg_copy_fromand pg_copy_tofunctions which copy to/from a PHP array, which may not be efficient for large data sets.

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

回答by sorin

There are several solutions:

有几种解决方案:

1 psqlcommand

1条psql命令

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

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

This has the big advantage that you can using it via SSH, like ssh postgres@host command- enabling you to get

这有一个很大的优势,您可以通过 SSH 使用它,例如ssh postgres@host command- 使您能够获得

2 postgres copycommand

2 postgrescopy命令

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

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

3 psql interactive (or not)

3 psql 交互(或不交互)

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

All of them can be used in scripts, but I prefer #1.

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

4 pgadmin but that's not scriptable.

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

回答by Marcin Wasiluk

In terminal (while connected to the db) set output to the cvs file

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

1) Set field seperator to ',':

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

\f ','

2) Set output format unaligned:

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

\a

3) Show only tuples:

3) 只显示元组:

\t

4) Set output:

4) 设置输出:

\o '/tmp/yourOutputFile.csv'

5) Execute your query:

5) 执行您的查询:

:select * from YOUR_TABLE

6) Output:

6) 输出:

\o

You will then be able to find your csv file in this location:

然后,您将能够在此位置找到您的 csv 文件:

cd /tmp

Copy it using the scpcommand or edit using nano:

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

nano /tmp/yourOutputFile.csv

回答by benjwadams

If you're interested in allthe columns of a particular table along with headers, you can use

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

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

This is a tiny bit simpler than

这比

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

which, to the best of my knowledge, are equivalent.

据我所知,它们是等效的。

回答by joshperry

CSV Export Unification

CSV 导出统一

This information isn't really well represented. As this is the second time I've needed to derive this, I'll put this here to remind myself if nothing else.

这些信息并没有得到很好的体现。由于这是我第二次需要推导出这个,如果没有别的,我会把它放在这里提醒自己。

Really the best way to do this (get CSV out of postgres) is to use the COPY ... TO STDOUTcommand. Though you don't want to do it the way shown in the answers here. The correct way to use the command is:

真正做到这一点的最好方法(从 postgres 中获取 CSV)是使用该COPY ... TO STDOUT命令。尽管您不想按照此处的答案中显示的方式进行操作。该命令的正确使用方法是:

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

Remember just one command!

只记住一个命令!

It's great for use over ssh:

它非常适合在 ssh 上使用:

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

It's great for use inside docker over ssh:

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

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

It's even great on the local machine:

在本地机器上它甚至很棒:

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

Or inside docker on the local machine?:

或者在本地机器上的 docker 里面?:

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

Or on a kubernetes cluster, in docker, over HTTPS??:

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

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

So versatile, much commas!

如此多才多艺,很多逗号!

Do you even?

你甚至?

Yes I did, here are my notes:

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

The COPYses

副本

Using /copyeffectively executes file operations on whatever system the psqlcommand is running on, as the user who is executing it1. If you connect to a remote server, it's simple to copy data files on the system executing psqlto/from the remote server.

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

COPYexecutes file operations on the server as the backend process user account (default postgres), file paths and permissions are checked and applied accordingly. If using TO STDOUTthen file permissions checks are bypassed.

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

Both of these options require subsequent file movement if psqlis not executing on the system where you want the resultant CSV to ultimately reside. This is the most likely case, in my experience, when you mostly work with remote servers.

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

It is more complex to configure something like a TCP/IP tunnel over ssh to a remote system for simple CSV output, but for other output formats (binary) it may be better to /copyover a tunneled connection, executing a local psql. In a similar vein, for large imports, moving the source file to the server and using COPYis probably the highest-performance option.

为简单的 CSV 输出配置通过 ssh 到远程系统的 TCP/IP 隧道之类的东西更复杂,但对于其他输出格式(二进制),/copy通过隧道连接执行本地psql. 同样,对于大型导入,将源文件移动到服务器并使用COPY可能是性能最高的选项。

PSQL Parameters

PSQL 参数

With psql parameters you can format the output like CSV but there are downsides like having to remember to disable the pager and not getting headers:

使用 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,,

Other Tools

其他工具

No, I just want to get CSV out of my server without compiling and/or installing a tool.

不,我只想在不编译和/或安装工具的情况下从我的服务器中获取 CSV。

回答by maudulus

I had to use the \COPY because I received the error message:

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

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

So I used:

所以我使用了:

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

and it is functioning

它正在运行

回答by Dirk Eddelbuettel

psqlcan do this for you:

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

See man psqlfor help on the options used here.

有关man psql此处使用的选项的帮助,请参阅。

回答by calcsam

I'm working on AWS Redshift, which does not support the COPY TOfeature.

我正在研究不支持该COPY TO功能的AWS Redshift 。

My BI tool supports tab-delimited CSVs though, so I used the following:

不过,我的 BI 工具支持制表符分隔的 CSV,因此我使用了以下内容:

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

回答by Lukasz Szozda

New version - psql 12 - will support --csv.

新版本 - psql 12 - 将支持--csv.

psql - devel

--csv

Switches to CSV (Comma-Separated Values) output mode. This is equivalent to \pset format csv.


csv_fieldsep

Specifies the field separator to be used in CSV output format. If the separator character appears in a field's value, that field is output within double quotes, following standard CSV rules. The default is a comma.

psql - 开发

--csv

切换到 CSV(逗号分隔值)输出模式。这等效于\pset 格式 csv


csv_fieldsep

指定要在 CSV 输出格式中使用的字段分隔符。如果分隔符出现在字段的值中,则该字段将按照标准 CSV 规则在双引号内输出。默认为逗号。

Usage:

用法:

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

回答by Amanda Nyren

In pgAdmin III there is an option to export to file from the query window. In the main menu it's Query -> Execute to file or there's a button that does the same thing (it's a green triangle with a blue floppy disk as opposed to the plain green triangle which just runs the query). If you're not running the query from the query window then I'd do what IMSoP suggested and use the copy command.

在 pgAdmin III 中,有一个选项可以从查询窗口导出到文件。在主菜单中,它是 Query -> Execute to file 或者有一个执行相同操作的按钮(它是一个带有蓝色软盘的绿色三角形,而不是仅运行查询的纯绿色三角形)。如果您没有从查询窗口运行查询,那么我会按照 IMSoP 的建议进行操作并使用复制命令。