如何将 CSV 数据插入 PostgreSQL 数据库（远程数据库）
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/33353997/
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
How to insert CSV data into PostgreSQL database (remote database )
提问by Spike
I have a CSV file. I can, using the command below, insert the data into a database if file is on the same server as the database:
我有一个 CSV 文件。如果文件与数据库在同一台服务器上，我可以使用下面的命令将数据插入到数据库中：
psql -h localhost -d local_mydb -U myuser -c "copy mytable (column1, column2) from '/path/to/local/file.csv' with delimiter as ','"
But the file is on a local server and the database is on another (remote) server.
但是文件在本地服务器上，数据库在另一个（远程）服务器上。
If I try to do this for a remote server, using the command below:
如果我尝试为远程服务器执行此操作，请使用以下命令：
psql -h remotehost -d remote_mydb -U myuser -c "copy mytable (column1, column2) from '/path/to/local/file.csv' with delimiter as ','"
I get a permission-denied exception.
我收到一个权限被拒绝的异常。
How can I insert data from a local file into a database on a remote server, without superuser privileges?
如何在没有超级用户权限的情况下将本地文件中的数据插入到远程服务器上的数据库中？
回答by Neil McGuigan
\copy(note the backslash) lets you copy to/from remote databases and does not require superuser privileges.
\copy（注意反斜杠）允许您复制到/从远程数据库复制，并且不需要超级用户权限。
psql -h remotehost -d remote_mydb -U myuser -c "\copy mytable (column1, column2) from '/path/to/local/file.csv' with delimiter as ','"
If you're using Java, you can use the CopyManager in the JDBC driver: https://jdbc.postgresql.org/documentation/publicapi/org/postgresql/copy/CopyManager.html
如果您使用的是 Java，则可以在 JDBC 驱动程序中使用 CopyManager：https://jdbc.postgresql.org/documentation/publicapi/org/postgresql/copy/CopyManager.html
回答by Schwern
You can feed the file via STDIN. From the PostgreSQL COPY documentation...
您可以通过 STDIN 提供文件。从PostgreSQL COPY 文档...
When STDIN or STDOUT is specified, data is transmitted via the connection between the client and the server.
当指定 STDIN 或 STDOUT 时，数据通过客户端和服务器之间的连接传输。
psql -h remotehost -d remote_mydb -U myuser -c \
"copy mytable (column1, column2) from STDIN with delimiter as ','" \
< /path/to/local/file.csv
I was incorrect about using FROM PROGRAM. It has the same caveats as FROM 'filename'. The server executes the program, not the client.
我关于使用FROM PROGRAM. 它与FROM 'filename'. 服务器执行程序，而不是客户端。
When PROGRAM is specified, the server executes the given commandand reads from the standard output of the program, or writes to the standard input of the program. The command must be specified from the viewpoint of the server, and be executable by the PostgreSQL user.
当指定 PROGRAM 时，服务器执行给定的命令并从程序的标准输出中读取，或写入程序的标准输入。该命令必须从服务器的角度指定，并且可由 PostgreSQL 用户执行。
回答by Mohit Singh
For local database, you will simply use:
对于本地数据库，您只需使用：
COPY TABLENAME FROM 'D:\xyz.csv' DELIMITER ',' CSV HEADER
For Server (remote database), you have to add \ :
对于服务器（远程数据库），您必须添加 \ ：
\COPY TABLENAME FROM 'D:\xyz.csv' DELIMITER ',' CSV HEADER
