从命令行导入 PostgreSQL CSV [英] PostgreSQL CSV import from command line

问题描述

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

I've been using the psql Postgres terminal to import CSV files into tables using the following

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

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

which works fine except that I have to be logged into the psql terminal to run it.

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

I would like to know if anyone knows of a way to do a command similar to this from the Linux shell command line similar to how Postgres allows a shell command like bellow

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

这允许从 Linux shell 转储数据库，而无需登录到 psql 终端.

This allows the dumping of a database from the Linux shell without being logged into psql terminal.

推荐答案

如 The PostgreSQL Documentation (II. PostgreSQL 客户端应用程序 - psql) 您可以使用开关 -c<将命令传递给 psql(PostgreSQL 交互式终端)/代码>.您的选择是:

As stated in The PostgreSQL Documentation (II. PostgreSQL Client Applications - psql) you can pass a command to psql (PostgreSQL interactive terminal) with the switch -c. Your options are:

执行 SQL COPY 命令但文件在客户端读取，内容路由到服务器.

perform the SQL COPY command but the file is read on the client and the content routed to the server.

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

(最初在这个答案中提到的客户端选项)

(client-side option originally mentioned in this answer)

读取服务器上的文件(当前用户需要有必要的权限):

reads the file on the server (current user needs to have the necessary permissions):

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

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

the DB roles needed for reading the file on the server:

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

COPY naming a file or command is only allowed to database superusers or users who are granted one of the default roles pg_read_server_files, pg_write_server_files, or pg_execute_server_program

PostgreSQL 服务器进程也需要访问该文件.

also the PostgreSQL server process needs to have access to the file.

这篇关于从命令行导入 PostgreSQL CSV的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！