Postgres: \copy syntax

Question

With PostgreSQL 9.5 on CentOS 7, I have created a database named sample along with several tables. I have .csv data in /home/MyUser/data for each table. For example, there exists TableName.csv for the table "TableName".

How do I load the csv files into each table?

---

What I've tried doesn't work and I can't figure out what I'm doing wrong.

Load from within the DB

$ psql sample

sample=# COPY "TableName" FROM '/home/MyUser/data/TableName.csv' WITH CSV;

ERROR:  could not open file "/home/MyUser/data/TableName.csv" for reading: Permission denied

This implies a file permission problem. All the files in data/ are -rw-r--r-- and the directory itself is drwxr-xr-x. So file permissions shouldn't be the problem (unless I'm missing something). The internet says that COPY has problems with permissions and to try \copy.

Load from CLI

$ psql \copy sample FROM /home/MyUser/data/TableName.csv WITH CSV

psql: warning: extra command-line argument "FROM" ignored
psql: warning: extra command-line argument "/home/MyUser/data/TableName.csv" ignored
psql: warning: extra command-line argument "WITH" ignored
psql: warning: extra command-line argument "CSV" ignored
psql: FATAL:  Peer authentication failed for user "sample"

This appears to be a syntax error, but I'm not finding the documentation particularly helpful (man psql then /\copy). I've also tried the following to the same result.

$ psql \copy sample."TableName" FROM /home/MyUser/data/TableName.csv WITH CSV
$ psql \copy sample FROM /home/MyUser/data/TableName.csv WITH DELIMITER ','

There are several other permutations which yield similar errors.

Web Resources Used

• https://www.postgresql.org/docs/9.5/static/app-psql.html

• https://www.postgresql.org/docs/9.5/static/sql-copy.html

• The correct COPY command to load postgreSQL data from csv file that has single-quoted data?

• https://soleil4716.wordpress.com/2010/08/19/using-copy-command-in-postgresql/

• Can I use \copy command into a function of postgresql?

• https://wiki.postgresql.org/wiki/COPY

Answer 1

About the permissions:

Don't forget that to access a file you need permissions on all directories in the path. So if, for example, the OS user postgres does not have permissions on the /home/MyUser directory, you get the observed error message.

About \copy:

You have to use the -c option to supply a command to psql:

$ psql -c "\copy sample FROM '/home/MyUser/data/TableName.csv' WITH (FORMAT CSV)"