我一直在使用 psql Postgres 终端使用以下方法将 CSV 文件导入表中

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

除了我必须登录到 psql 终端才能运行它之外，它工作正常。

我想知道是否有人知道从 Linux shell 命令行执行类似于此命令的方法，类似于 Postgres 如何允许像下面这样的 shell 命令

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

这允许在不登录 psql 终端的情况下从 Linux shell 转储数据库。

最佳答案

已接受答案中的解决方案仅适用于服务器，并且当执行查询的用户将有权读取文件时，如 this SO answer 中所述.

否则，更灵活的方法是替换 SQL 的 COPY带有 psql 's "meta-command" called \copy 的命令其中which takes all the same options as the "real" COPY, but is run inside the client (最后不需要 ;):

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

As per docs , \copy命令:

Performs a frontend (client) copy. This is an operation that runs an SQL COPY command, but instead of the server reading or writing the specified file, psql reads or writes the file and routes the data between the server and the local file system. This means that file accessibility and privileges are those of the local user, not the server, and no SQL superuser privileges are required.

此外，如果 the_file.csv包含第一行的标题，可以通过添加header来识别在上述命令的最后:

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

https://stackoverflow.com/questions/28602647/