Export Postgres Database Table Data to File

Many times we need the data from tables in file formats. It could be a plain text file or a csv file. Let's look into various ways of exporting the data out of postgres table.

Export Postgres table to csv

csv stands for comma separated file and is a very popular format for opening the file in excel. To export csv we can follow one of the following ways:

• Let's assume we have a Student table in the database. So to export this table data with header in csv file, enter into psql console and issue the following command. Assuming the file will be generated at D drive. For Linux you can change the file path accordingly.

             copy Student to 'D:\student.csv' csv header
       
         A file with student.csv will be created

• If you want certain columns only for Student table, then issue the following command from command prompt

              psql -U <db user name> <db_name> -F , --no-align -c "SELECT id,name from Student" > student.csv

• For tab delimited text file

      psql -U <db user name> <db_name> -F $'\t' --no-align -c "SELECT id,name from Student" > studentwithTabs.txt

Export Postgres table to File

Enter into psql console. In the console, issue the following command

             \o output.txt

Now whatever command you will fire, the result will go into output.txt file.  Giving the \o command without file name will start putting the output back into console.