Exporting data from Postgres as a CSV or tab delimited file can be fairly simple using the COPY
command. Examples below.
Export an entire table as CSV:
psql -c "COPY my_table TO STDOUT WITH CSV" > output.csv
Export an entire table as CSV with Header:
psql -c "COPY my_table TO STDOUT WITH CSV HEADER" > output.csv
Export query as CSV:
psql -c "COPY (SELECT first_name, last_name FROM my_table) TO STDOUT WITH CSV" > output.csv
Export query as data tab delimited text file:
psql -c "COPY (SELECT first_name, last_name FROM my_table) TO STDOUT" > output.txt
Export data directly to file (requires superuser permissions):
psql -c "COPY (SELECT first_name, last_name FROM my_table) TO '/home/user/file.txt'"