this version is by far the best as the COPY command requires admin access
– Matthew O'RiordanNov 17 '12 at 12:51

2

Also with the psql approach, one can save the output anywhere one has access to. I just used the psql approach to get data from a remote server into a local file. Very slick.
– Ian GowJul 17 '14 at 18:15

Much better, especially when saving to a directory where you have access but the postgres user doesn't.
– Steve BennettApr 7 '15 at 7:28

I don't believe you need the terminating semicolon in the psql version of the command (\COPY ...). And at least in my version of psql (9.5.2) I didn't need to specify 'DELIMITER'; the default was a comma.
– user1071847Sep 6 '16 at 15:36

The best for "any environment". Best for 1. Not need special permissions at Postgresql or at client; 2. can use relative path; and 3. is secure for real CSV format (secure quotations).
– Peter KraussMar 30 '17 at 16:46

I like this, without the -F ,, and use | as the separator. Thanks!
– dsummerslNov 17 '16 at 19:41

2

This is not what is commonly considered as an Export feature, just controlled data display. The difference is light, but important: this is more intended to be read by a human than the COPY statement which creates a file to be reused
– Romain GJan 27 '17 at 10:06

7

DANGER it is not for CSV format, not works for arrays or text with "," .. not do proper CSV-quotation. Use @Brian's answer.
– Peter KraussMar 30 '17 at 16:43

I am posting this answer because none of the other answers given here actually worked for me. I could not use COPY from within Postgres, because I did not have the correct permissions. So I chose "Export grid rows" and saved the output as UTF-8.

The psql version given by @Brian also did not work for me, for a different reason. The reason it did not work is that apparently the Windows command prompt (I was using Windows) was meddling around with the encoding on its own. I kept getting this error:

ERROR: character with byte sequence 0x81 in encoding "WIN1252" has no equivalent in encoding "UTF8"

The solution I ended up using was to write a short JDBC script (Java) which read the CSV file and issued insert statements directly into my Postgres table. This worked, but the command prompt also would have worked had it not been altering the encoding.