如何将CSV数据插入到远程PostgreSQL数据库中

Question

我有一个CSV文件。如果文件在与数据库相同的服务器上，我可以使用以下命令将数据插入到数据库中：

psql -h localhost -d local_mydb -U myuser -c "copy mytable (column1, column2)  from '/path/to/local/file.csv' with delimiter as ','"

但文件位于本地服务器上，数据库则在另一台（远程）服务器上。

如果我尝试对远程服务器使用以下命令：

psql -h remotehost -d remote_mydb -U myuser -c "copy mytable (column1, column2)  from '/path/to/local/file.csv' with delimiter as ','"

我收到了一个权限被拒绝的异常。

我如何在没有超级用户权限的情况下，从本地文件将数据插入到远程服务器上的数据库中？

- Spike

Answer 1

psql中的\copy（注意反斜杠）可以让你从/向远程数据库进行复制，而且不需要超级用户权限。

psql \
  -h $hostname -d $dbname -U $username \
  -c "\copy mytable (column1, column2)  from '/path/to/local/file.csv' with delimiter as ','"

https://www.postgresql.org/docs/current/sql-copy.html

- Neil McGuigan

---

我尝试了你和Schwern的方法，从控制台上看它可以正常工作。但是，当我尝试在Java代码中使用时，会出现“\”语法错误。 - Spike

请注意，\copy 是 psql 命令（http://www.postgresql.org/docs/current/static/app-psql.html#APP-PSQL-META-COMMANDS-COPY）。它在其他上下文中无法使用。 - Schwern

以防对其他人有所帮助。如果你收到“缺少列数据”的错误提示，那是因为你忘记指定分隔符了。 - Abe Petrillo

干得好...:-) - Gowtham Balusamy

如果数据中包含逗号（例如：“123 main st，new york，usa”），则使用分隔符“，”不起作用。请改为使用“CSV”。 - mehmet

Answer 2

您可以通过 STDIN 提供文件。来自PostgreSQL COPY 文档...

当指定 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

---

我关于使用 FROM PROGRAM 是错误的。它与 FROM 'filename' 有相同的注意事项。服务器执行程序，而不是客户端。

当指定了 PROGRAM 时，服务器会执行给定的命令，并从程序的标准输出读取，或向程序的标准输入写入。该命令必须从服务器的视角指定，并且可由 PostgreSQL 用户执行。

- Schwern

Answer 3

对于本地数据库，您只需使用：

COPY TABLENAME FROM 'D:\xyz.csv' DELIMITER ',' CSV HEADER

对于服务器（远程数据库），您必须添加 \ ：

\COPY TABLENAME FROM 'D:\xyz.csv' DELIMITER ',' CSV HEADER

- Mohit Singh