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;
which works fine except that I have to be logged into the psql terminal to run it.
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
This allows the dumping of a database from the Linux shell without being logged into psql terminal.
Advertisement
Answer
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:
1, Client-side CSV: copy
meta-command
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)
2. Server-side CSV: SQL COPY
command
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
naming a file or command is only allowed to database superusers or users who are granted one of the default rolespg_read_server_files
,pg_write_server_files
, orpg_execute_server_program
also the PostgreSQL server process needs to have access to the file.