PostgreSQL CSV import from command line
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"
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.
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
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;"