When connected to a database using psql, I would normally use the
\copy meta-command to export data; but Redshift doesn’t seem to have implemented that (however it actually works, under the hood).
It is possible to copy data to S3, but you need an available bucket, and all the necessary IAM nonsense.
The simplest thing I’ve found, so far, is to set the output format:
\pset format csv
and then the output file:
before running your query:
select * ...
or from the cmd line:
psql ... -c "select ..." -o 'foo.csv' --csv