COPY FROM CSV to Postgres in Ubuntu

Question

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

Zach · Accepted Answer · 2013-05-31 18:09:55Z

2

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.

answered May 31, 2013 at 18:09

Zach

1851 silver badge12 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

simonp · Accepted Answer · 2013-05-30 22:42:58Z

0

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

answered May 30, 2013 at 22:42

simonp

2,8871 gold badge16 silver badges19 bronze badges

5 Comments

Zach Over a year ago

you're right on the -rw------- but chmod g+r,o+r /tmp/sisinst_bio.csv gave me this error: <br> chmod: changing permissions of ‘/tmp/sisinst_bio.csv’: Operation not permitted.

zxq9 Over a year ago

First, don't connect to Postgres as postgres unless performing initial setup admin. Second, chown the file to whatever user you are connecting as. If you can't chown or chmod the file, you may have to sudo chown, or copy it to a new location that you can control (if Mint 15 has a special trapping mechanism for /tmp -- which may be the case).

Zach Over a year ago

Ok, I've changed the ownership of the file using chown. I'm also using a new user in Postgres (zach). I run ls -l /home/zach/Documents/Postgres91/sisinst_bio.csv. The file is owned in this way: -rw-rw---- 1 zach zach 90722 may 30 12:39 and I'm still receiving the same error in Postgres. Does this mean that it is owned by the user zach and group zach?

simonp Over a year ago

Yes - this means it's owned by user zach and group zach. -rw-rw---- means it's readable and writable by the owning user, and by any user in the owning group. chmod should now work. chmod o+r /home/zach/Documents/Postgres91/sisinst_bio.csv will make it readable by any user.

simonp Over a year ago

I should probably clarify: Postgres users and UNIX users are not the same thing. When you do a COPY, the copy runs on the server as whatever Unix user the server is running as. That's why even though you have a Unix user named "zach" and a Postgres user named "zach", you still can't access the file. Making the file readable by any user will ensure the Pg server can read it. The alternative is to make the file owned by the Pg server user.

Collectives™ on Stack Overflow

COPY FROM CSV to Postgres in Ubuntu

2 Answers 2

Comments

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related