Outputting from Postgres to CSV

I can never remember how to output to a CSV file from postgres, and end up having to google it time and time again – so I’m making a note of it here mostly for my own use 🙂

f ','
o /tmp/moocow.csv
SELECT foo,bar FROM whatever;

If a field has newlines, this will break. You can do something like this instead…..

 SELECT foo, bar, '"' || REPLACE(REPLACE(field_with_newilne, 'n', '\n'), '"', '""') || '"' FROM whatever;

21 thoughts on “Outputting from Postgres to CSV”

  1. After much pain with line endings:

    COPY (SELECT foo,bar FROM whatever) TO ‘/tmp/dump.csv’ WITH CSV HEADER

  2. I run a lot of queries from the bash command line or from scripts, so here’s a tip:

    If you run into this error: “ERROR: must be superuser to COPY to or from a file,” try this:

    echo “COPY (SELECT foo from BAR) TO STDOUT with CSV HEADER” | psql -o filename.csv database_name

  3. @Ben,

    Your solution works well in version 8.4.2 and it shows a syntax error in version 8.1

    copy to file command in postgresql 8.1 does not accept a query . it can copy whole table but not a query

    do u have any solution for 8.1?

  4. Your post was useful

    Could you tell me how I can include the data in double quotes ?

    o /tmp/moocow.csv
    SELECT foo,bar FROM whatever;

    and the output like


    Appreciate your help !

    1. psql “dbname=dbname host=host user=user password=password port=port” -F , –no-align -c “select * from schema.table limit 10” > /path/sql_output.csv

      I’ve tried this and output is coming with | (pipe) not with , (comma).

      Where I’m doing the wrong ?

  5. I have to export several queries from different tables but the COPY command always overwrites the file.
    Is there any solution for this ?

    1. @sonia,

      You can do like this,
      COPY (SELECT foo,bar FROM whatever) TO ‘/tmp/dump.csv’
      delimiters ‘.’ WITH CSV HEADER


    1. Is there a way to modify the query below to output a filename with today’s date or current date (MMDDYY)? For example: filename_030414.csv (where 03=month, 04=day, 14=year)?

      copy (select * from table) to ‘filename’ csv header

Leave a Reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.