Output data from PostgreSQL as a CSV without use of COPY

Here’s a one-line command that bypasses the necessity of the COPY privilege in being able to output data in comma-separated value (CSV) format:

psql -U username -h -W database -F ',' -t -A -c 'SELECT * FROM Users' -o outputfile.csv

Also useful is taking input from a file (which helps avoid issues with quote characters):

psql -U username -h -W database -F ',' -t -A -f input.sql -o outputfile.csv

You’re probably already familiar with most of these options, but the less common ones are:

  • -F changes the field separator to the , character
  • -t outputs the rows without their column names (which seems to undo the use of -F)
  • -A un-centers the row output
  • -c contains the actual query you wish to run
  • -f takes input of the query you wish to run from a file
  • -o redirects the output of the query to the specified file

Leave a Reply

Your email address will not be published. Required fields are marked *