PostgreSQL CSV import from command line

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.

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 roles pg_read_server_files, pg_write_server_files, or pg_execute_server_program

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