There are several solutions:

1 psql command

psql -d dbname -t -A -F"," -c "select * from users" > output.csv

This has the big advantage that you can using it via SSH, like ssh postgres@host command - enabling you to get

2 postgres copy command

COPY (SELECT * from users) To '/tmp/output.csv' With CSV;

3 psql interactive (or not)

>psql dbname
psql>\f ','
psql>\a
psql>\o '/tmp/output.csv'
psql>SELECT * from users;
psql>\q

All of them can be used in scripts, but I prefer #1.

4 pgadmin but that's not scriptable.

In terminal (while connected to the db) set output to the cvs file

1) set field seperator to ',' by:

\f ','

2) set output format unaligned:

\a

3) show only tuples

\t

4) set output

\o '/tmp/yourOutputFile.csv'

5) execute your query

select * from YOUR_TABLE

6) output

\o

you will be able to find your cvs file in this location

cd //tmp
copy it using scp command 

or edit using nano:

nano //tmp/yourOutputFile.csv

If you're interested in all the columns of a particular table along with headers, you can use

COPY table TO '/some_destdir/mycsv.csv' WITH CSV HEADER;

This is a tiny bit simpler than

COPY (SELECT * FROM table) TO '/some_destdir/mycsv.csv' WITH CSV HEADER;

which, to the best of my knowledge, are equivalent.

I had to use the \COPY because I received the error message:

ERROR:  could not open file "/filepath/places.csv" for writing: Permission denied

So I used:

\Copy (Select address, zip  From manjadata) To '/filepath/places.csv' With CSV;

and it is functioning

psql can do this for you:

edd@ron:~$ psql -d beancounter -t -A -F"," \
                -c "select date, symbol, day_close " \
                   "from stockprices where symbol like 'I%' " \
                   "and date >= '2009-10-02'"
2009-10-02,IBM,119.02
2009-10-02,IEF,92.77
2009-10-02,IEV,37.05
2009-10-02,IJH,66.18
2009-10-02,IJR,50.33
2009-10-02,ILF,42.24
2009-10-02,INTC,18.97
2009-10-02,IP,21.39
edd@ron:~$

See man psql for help on the options used here.

In pgAdmin III there is an option to export to file from the query window. In the main menu it's Query -> Execute to file or there's a button that does the same thing (it's a green triangle with a blue floppy disk as opposed to the plain green triangle which just runs the query). If you're not running the query from the query window then I'd do what IMSoP suggested and use the copy command.

I'm working on AWS Redshift, which does not support the COPY TO feature.

My BI tool supports tab-delimited CSVs though, so I used the following:

 psql -h  dblocation  -p port -U user  -d dbname  -F $'\t' --no-align -c " SELECT *   FROM TABLE" > outfile.csv

I've written a little tool called psql2csv that encapsulates the COPY query TO STDOUT pattern, resulting in proper CSV. It's interface is similar to psql.

psql2csv [OPTIONS] < QUERY
psql2csv [OPTIONS] QUERY

The query is assumed to be the contents of STDIN, if present, or the last argument. All other arguments are forwarded to psql except for these:

-h, --help           show help, then exit
--encoding=ENCODING  use a different encoding than UTF8 (Excel likes LATIN1)
--no-header          do not output a header

JackDB, a database client in your web browser, makes this really easy. Especially if you're on Heroku.

It lets you connect to remote databases and run SQL queries on them.

                                                                                                                                                       ^Source

Once your DB is connected, you can run a query and export to CSV or TXT (see bottom right).

^{Note: I'm in no way affiliated with JackDB. I currently use their free services and think it's a great product.}

If you have longer query and you like to use psql then put your query to a file and use the following command:

psql -d my_db_name -t -A -F";" -f input-file.sql -o output-file.csv