COPY FROM CSV to Postgres in Ubuntu

问题:

My question is similar to this one but in Linux Mint 15 (Ubuntu). I've tried the standard COPY (which I used on Windows all the time):

COPY public.bio FROM 'tmp/sisinst_bio.csv' DELIMITERS '|' CSV;

I receive this error:

ERROR: could not open file "/tmp/sisinst_bio.csv" for reading: Permission denied

The owner and user of the database is postgres.

Tries

(1) Creating a user that is the same as my user account for Ubuntu (zach) and changing the owner of the database.

(2) Moving the csv to various parts of Ubuntu

sudo cp -r /home/zach/Documents/Postgres91/sisinst_postgresql_bio_test.csv /usr/share/postgresql/9.1

sudo cp -r /home/zach/Documents/Postgres91/sisinst_postgresql_bio_test.csv /tmp

This is really easy but I'm stuck. I'm not sure if this is Postgresql or Ubuntu problem.

Update

Does this have anything to do with this?

As always, thanks all

回答1:

For the Permissions in the CSV (Right Click -> Properties -> Permissions), the Others was set to None.

To fix this so that Postgres users can use it, I changed Others to Read-only which worked.

Ultimately, it might be better have Postgres users in a Group but that's above me at this point.

I'm open to a better answer.

回答2:

Use ls -l /tmp/sisinst_bio.csv to see what the permissions on the file are. Chances are you'll see something like -rw-------, which means it's readable and writeable by the owner, but not readable by anyone else.

If that's the case, try making the CSV file group- and world-readable, using:

chmod g+r,o+r /tmp/sisinst_bio.csv