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)"

Answer 2

Adding, this sample was helpful from Laurenz, as I got this to work first time today from a windows 10 client.

My target is an enterprise greenplum db (same thing just parallel/scale).

Step 1, Postgres client installed (I didn't install the database itself, but all the other items as part of the windows bundling install, including the shell most importantly for this) https://www.postgresql.org/download/

Step 2, Create your target table on your workspace/db. I use DBeaver to connect to greenplum db, then run this, you don't have to use DBeaver, you can probably run this in the shell itself. CREATE TABLE workspacename.scott_test2 ( blah1 varchar(40) NOT NULL, blah2 varchar(40) NOT NULL ) DISTRIBUTED BY (blah1);

Step 3, launch the shell command from postgres utilities installed earlier, login as it prompts you, then when you're in, the command I ran in that shell installed above (a command line interface) is simply: \copy workspacename.scott_test2 FROM 'C:\temp\flatfile.csv' WITH CSV;

Note \copy is not copy. Use \copy

This loaded a half million row table in 2 seconds, fast. file above is comma delimited, the with CSV does that.