Postgres: COPY TO error

Question

I need help turning my Postgres table into a CSV file. First, I type

sudo su postgres (then I enter my password)
psql -d postgres -U postgres

The terminal looks like:

postgres=#

I typed in the COPY TO command to try to create a .csv file with all the information in my table, but I get an error:

postgres=# COPY venues TO '/usr/file.csv';
ERROR:  could not open file "/usr/file.csv" for writing: Permission denied

I also tried using the \copy command, but I get:

postgres=# \COPY venues TO '/usr/file.csv';
/usr/file.csv: Permission denied

I read a couple answers that said that I might not have permission to write in a certain directory. Is this true?

Also, in postgres, the schema lists my_name as the owner of the table I am trying to export information from (venues). However, this is when I am logged in as a postgres user, and I am only able to access the table when I am a logged is as a postgres user.

When I log into postgres by simply typing "psql," I get:

my_name=>

and when I type the command "\d" to see the tables under this user, I do not see the venues table listed. Can someone please help me with this issue? Thank you in advance.

"I might not have permission to write in a certain directory. Is this true?" Your error messages says "Permission denied", what do you think? — Mat
– Mat, Commented Jul 1, 2015 at 11:11
When typying psql without arguments you're logging into a different database than psql -d postgres -U postgres, which is why it doesn't have the same tables. — Daniel Vérité
– Daniel Vérité, Commented Jul 1, 2015 at 11:35
@Mat I wasn't sure what that "Permission denied" was specifically for. That is why I asked — hsivru
– hsivru, Commented Jul 1, 2015 at 11:50
@hsivru: instead of asking, why didn't you go and check directly? Much faster than typing that whole question here. — Mat
– Mat, Commented Jul 1, 2015 at 11:55
I guess @hsivru meant he was not sure if he has no permission to write to file or to read a table or to run a command... Well it was a file for sure — Vao Tsun
– Vao Tsun, Commented Jul 1, 2015 at 12:03

Vao Tsun · Accepted Answer · 2015-07-01 12:04:42Z

1

postgres=# COPY venues TO '/tmp/file.csv';

should work for u

answered Jul 1, 2015 at 12:04

Vao Tsun

52.5k13 gold badges114 silver badges149 bronze badges

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

4 Comments

hsivru Over a year ago

thanks! this worked. how come I was able to transfer and create a file in the tmp folder and not in the usr folder? I did change the permissions of the file in the usr folder, but I got the same error as the one is wrote above.

Craig Ringer Over a year ago

/usr is not writeable by users by default. It's for application and system files. Why would you try to write to /usr? Surely you'd use \copy and /home/myusername...? If you don't specify an absolute path that's what'll happen, e.g. \copy venues TO 'file.csv' will store file.csv in $HOME/file.csv.

hsivru Over a year ago

@CraigRinger I did try writing to a file in my /home/myusername/ file, but I was still getting the "Permission denied" message. I was only adding to /usr to check if I am able to create a file from the postgres command line--I wasn't aware that I couldn't add to this file.

Vao Tsun Over a year ago

/home/myusername/ not necessarily is writable for user that runs posmaster daemon... this way \copy will work, cos \copy is psql (clientside) command and COPY won't (serverside)

Collectives™ on Stack Overflow

Postgres: COPY TO error

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related