14

I have read-only access to a database on a remote server. So, I can execute:

COPY products TO '/tmp/products.csv' DELIMITER ',';

But on that server I don't have permissions to create/save a file, so I need to do this on my local machine.

When I connect to the remote database, how can I execute a command to save the file on my local machine instead of the remote server?

Or, how can I execute a Linux command to connect to the remote database, execute a query, and save the output as a file to my local machine?

29

Both the approaches already suggested appear to be unnecessarily complicated.

Just use psql's built-in \copy command, which works just like server-side COPY but does a copy over the wire protocol to the client and uses client paths.

Because it's a psql backslash command you omit the trailing semicolon, eg:

\copy products TO '/tmp/products.csv' CSV DELIMITER ','

See the \copy entry in the manual for the psql command and the COPY command documenation for more detail.

Just like COPY you can use \copy with a (SELECT ...) query instead of a table name when copying data out (but not in).


A generally inferior alternative that can be useful in a few limited situations is to use:

psql -t -P format=unaligned -P fieldsep_zero=on -c 'SELECT * FROM tablename'

and use the -o flag or shell output redirection to write the output to a file. You should almost always use \copy in preference to this.

  • But this doesn't allow for transactions :( – Reza S Dec 24 '15 at 2:35
  • Um, sure it does. Use a here-document to feed psql a script, starting with BEGIN, then doing your \copy commands, then a COMMIT. Or use psql -f to run a script rather than using a here-document. – Craig Ringer Dec 24 '15 at 2:36
  • Thanks for getting back... that's what I ended up doing and it worked =) – Reza S Dec 24 '15 at 4:29
  • You can use -A instead of -P format=unaligned and also i think you need a -P fieldsep=, – Evan Carroll Nov 8 '17 at 3:25
2

The Linux command is:

psql -h 127.0.0.1 -U username -o file.csv -c 'select id, name from clients;'
  • 1
    That won't produce CSV, it'll produce formatted text output. If you added -t -P format=unaligned to that command you'd get something a little closer, like buggy pipe-delimited CSV, but pipes in the text wouldn't get escaped so it'd be invalid. – Craig Ringer Mar 8 '13 at 14:21
  • Oh, you'd also want -P fieldsep=',' except that this would be even more likely to cause errors due to lack of escaping. -P fieldsep_zero=on would be OK if you didn't mind parsing null-byte-delimited text, as null bytes can't occur in psql output naturally. – Craig Ringer Mar 8 '13 at 14:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.