0

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.

Vao Tsun
  • 47,234
  • 13
  • 100
  • 132
hsivru
  • 55
  • 1
  • 8
  • 2
    "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 Jul 01 '15 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é Jul 01 '15 at 11:35
  • @Mat I wasn't sure what that "Permission denied" was specifically for. That is why I asked – hsivru Jul 01 '15 at 11:50
  • @hsivru: instead of asking, why didn't you go and check directly? Much faster than typing that whole question here. – Mat Jul 01 '15 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 Jul 01 '15 at 12:03

1 Answers1

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

should work for u

Vao Tsun
  • 47,234
  • 13
  • 100
  • 132
  • 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. – hsivru Jul 01 '15 at 12:19
  • `/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`. – Craig Ringer Jul 02 '15 at 00:19
  • @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. – hsivru Jul 02 '15 at 07:27
  • 1
    /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) – Vao Tsun Jul 02 '15 at 07:30