PostgreSQL 的 COPY 语句
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8712421/
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
PostgreSQL's COPY statement
提问by vchitta
Using COPY statement of PostgreSQL, we can load data from a text file into data base's table as below:
使用 PostgreSQL 的 COPY 语句，我们可以将文本文件中的数据加载到数据库的表中，如下所示：
COPY CME_ERROR_CODES FROM E'C:\\Program Files\\ERROR_CODES\\errcodes.txt' DELIMITER AS '~'
COPY CME_ERROR_CODES FROM E'C:\\Program Files\\ERROR_CODES\\errcodes.txt' DELIMITER AS '~'
The above statement is run from a machine which has postgresql client where as the server is in another windows machine. Running the above statement is complaining me that ERROR: could not open file "C:\Program Files\ERROR_CODES\errcodes.txt" for reading: No such file or directory.
上面的语句是从一台有 postgresql 客户端的机器上运行的，而服务器在另一台 Windows 机器上。运行上面的语句是在抱怨我ERROR: could not open file "C:\Program Files\ERROR_CODES\errcodes.txt" for reading: No such file or directory。
After some research, i observed that COPYstatement is looking for the loader file(errcodes.txt) in the postgresql server's machine at the same path (C:\Program Files\ERROR_CODES). To test this , i have create the same folder structure in the postgresql server's machine and kept the errcodes.txt file in there. Then the COPY statement worked well. It looks very tough constraint for me with COPY statement.
Is there any setting needed to avoid this? or it is the behavior of COPY statement? I didn't find any information on PostgreSQL documents.
经过一些研究，我观察到该COPY语句正在 postgresql 服务器的机器中在同一路径 (C:\Program Files\ERROR_CODES) 中查找加载程序文件 (errcodes.txt)。为了测试这一点，我在 postgresql 服务器的机器上创建了相同的文件夹结构，并将 errcodes.txt 文件保存在那里。然后 COPY 语句运行良好。COPY 语句对我来说看起来非常严格。是否需要任何设置来避免这种情况？还是 COPY 语句的行为？我没有找到有关 PostgreSQL 文档的任何信息。
回答by filiprem
here's the standard solution:
这是标准解决方案：
COPY foo (i, j, k) FROM stdin;
1<TAB>2<TAB>3
\.
The data must be properly escaped and tab-separated.
数据必须正确转义并以制表符分隔。
Actually, it is in the docs, even in grammar definition you have STDIN... See: http://www.postgresql.org/docs/9.1/static/sql-copy.html
实际上，它在文档中，即使在语法定义中你也有 STDIN ......参见：http: //www.postgresql.org/docs/9.1/static/sql-copy.html
If you're using some programming language with COPY support, you will have pg_putcopy or similar function. So you don't have to worry about escaping and concatenation.
如果您使用某种支持 COPY 的编程语言，您将拥有 pg_putcopy 或类似功能。所以你不必担心转义和串联。
Hints how to do this manually in Python -> Recreating Postgres COPY directly in Python?
提示如何在 Python 中手动执行此操作 ->直接在 Python 中重新创建 Postgres COPY？
The Perl way -> http://search.cpan.org/dist/DBD-Pg/Pg.pm#COPY_support
Perl 方式 -> http://search.cpan.org/dist/DBD-Pg/Pg.pm#COPY_support
Hope this helps.
希望这可以帮助。
回答by Gavin
From the documentation
从文档
Quote: COPY with a file name instructs the PostgreSQL server to directly read from or write to a file. The file must be accessible to the server and the name must be specified from the viewpoint of the server. When STDIN or STDOUT is specified, data is transmitted via the connection between the client and the server.
引用： 带有文件名的 COPY 指示 PostgreSQL 服务器直接读取或写入文件。服务器必须可以访问该文件，并且必须从服务器的角度指定名称。当指定 STDIN 或 STDOUT 时，数据通过客户端和服务器之间的连接传输。
If you want to copy from a local machine file to a server use \copy command.
如果您想从本地机器文件复制到服务器，请使用 \copy 命令。
