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.

回答1:

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

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

回答2:

The solution in the accepted answer will only work on the server and when the user executing the query will have permissions to read the file as explained in this SO answer.

Otherwise, a more flexible approach is to replace the SQL's COPY command with the psql's "meta-command" called \copy which which takes all the same options as the "real" COPY, but is run inside the client (with no need for ; at the end):

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

As per docs, the \copy command:

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.

---

In addition, if the the_file.csv contains the header in the first line, it can be recognized by adding header at the end of the above command:

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

回答3:

The most flexible way is to use a shell HERE document, which allows you to use shell variables inside your query, even inside (double or single) quotes:

#!/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

回答4:

To complete the previous answer, I would suggest:

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