从命令行导入PostgreSQL CSV数据

Question

我一直在使用psql Postgres终端将CSV文件导入表格，方法如下：

COPY tbname FROM
'/tmp/the_file.csv'
delimiter '|' csv;

除了需要登录到psql终端才能运行它之外，它的运作正常。

我想知道是否有人知道一种类似于Postgres允许以下命令的方法，可以在Linux shell命令行上执行此命令：

/opt/postgresql/bin/pg_dump dbname > /tmp/dbname.sql

这样可以在Linux shell中进行数据库转储，而无需登录到psql终端。

- TheLovelySausage

Answer 1

接受答案中的解决方案仅适用于服务器，并且当执行查询的用户具有读取文件的权限时才能工作，如此SO答案所述。

否则，一种更灵活的方法是将SQL的COPY命令替换为psql的“元命令”\copy，它具有与“真实”COPY相同的所有选项，但在客户端内运行（无需在结尾处使用;）：

psql -c "\copy tbname FROM '/tmp/the_file.csv' delimiter '|' csv"

根据文档，\copy 命令：

执行前端（客户端）拷贝。这是运行 SQL COPY 命令的操作，但是服务器不会读取或写入指定的文件，而是由 psql 读取或写入该文件，并在服务器和本地文件系统之间路由数据。这意味着文件可访问性和权限是本地用户的，而不是服务器的，不需要 SQL 超级用户权限。

---

此外，如果the_file.csv文件的第一行是标题，可以在上述命令的末尾添加header来识别它：

psql -c "\copy tbname FROM '/tmp/the_file.csv' delimiter '|' csv header"

- Dmitri Zaitsev

---

这是最佳答案，因为生产数据库肯定不应该允许用户本地登录和执行，并且在数据库上公开文件系统共享也是一个坏主意。当文件位于可从数据库和用户访问的网络共享上时，可以安全地考虑使用COPY。在大多数情况下，这种\copy方式更好。谢谢！ - Brian Bulkowski

Answer 2

如PostgreSQL文档所述 (II. PostgreSQL客户端应用程序 - psql)，您可以使用开关-c将命令传递给psql(PostgreSQL交互式终端)。以下是您的选项:

1、客户端CSV： \copy 元命令

执行SQLCOPY命令，但文件在客户端上读取，内容路由到服务器。

psql -c "\copy tbname FROM '/tmp/the_file.csv' delimiter '|' csv"

（客户端选项最初在此答案中提到）

2. 服务器端 CSV：使用 SQL COPY 命令

在服务器上读取文件（当前用户需要具备必要的权限）：

psql -c "COPY tbname FROM '/tmp/the_file.csv' delimiter '|' csv;"

在服务器上读取文件所需的数据库角色：

COPY 命令只允许数据库超级用户或被授予以下默认角色之一的用户使用：pg_read_server_files、pg_write_server_files 或 pg_execute_server_program

此外，PostgreSQL 服务器进程需要访问该文件。

- Simo Kivistö

Answer 3

为了完成之前的 答案，我建议：

psql -d your_dbname --user=db_username -c "COPY tbname FROM '/tmp/the_file.csv' delimiter '|' csv;"

- Andrea Araldo

Answer 4

最灵活的方法是使用shell HERE文档，它允许您在查询中使用shell变量，甚至包括（双引号或单引号）：

最灵活的方式是使用Shell的HERE文档，它可以让您在查询中使用shell变量，甚至在（双引号或单引号）中也可以使用：

#!/bin/sh

THE_USER=moi
THE_DB=stuff
THE_TABLE=personnel
PSQL=/opt/postgresql/bin/psql
THE_DIR=/tmp
THE_FILE=the_file.csv

${PSQL} -U ${THE_USER} ${THE_DB} <<OMG
COPY ${THE_TABLE} FROM '${THE_DIR}/${THE_FILE}' delimiter '|' csv;
OMG

- wildplasser

Answer 5

你可以尝试以下方法：

psql -d mydatabase -c "\copy mytable from 'filename.csv' with (format csv, header true)"

不要忘记你需要从文件所在的位置运行命令。

- Salim Nadji