In this post, we will see how we can export our PostgreSQL table data or query result to a CSV (Comma Separated Value) file.

We will create a shell script for exporting the data. Also, to define our queries, we will create separate .sql files.

I assume following for this example:

Database          :   mydb
User              :   myuser
Table             :   employees
Output CSV        :   employees.csv
Operating System  :   Linux or Mac

Please replace above values according to your environment.

Now, we will create following directories at whatever location we want on our computer:

export
    queries
    output

You can give any name of your choice to the above directories.
Next, we will create our SQL file and save it in queries directory.

Create a new file named employees.sql and save it in export->queries directory with following text:

select * from employees

Now, we will create our shell script file named export-data.sh in export directory with following code:

if [ "$1" = "" ]; then
         echo "Usage: sh export-data.sh sql_file_name"
else
        QUERY_NAME=$1
        $PATH_TO_SAVE='/home'
        QUERY_FILE_NAME="queries/$QUERY_NAME.sql"
        QUERY_TEXT=`cat $QUERY_FILE`

        psql -U myuser -d mydb -c "\copy ($QUERY_TEXT) TO '$PATH_TO_SAVE/$QUERY_NAME.csv' CSV HEADER"

        echo "Data exported to file $QUERY_NAME.sql"
fi

Now, we are ready to export our data to csv. Run the script we just created with following command:

sh export-data.sh employees

Shell script explanation

  1. We need to send command line arguments to our shell script. The index of command line starts with $0, $1 …
  2. $0 is the name of script which is export-data.sh, $1 is the name of sql file we want to run
  3. Hence, we made sure that $1 should not be empty. If it is not provided, we printed how to run the program
  4. The line QUERY_TEXT=`cat $QUERY_FILE` uses the Linux/Mac cat command to read the contents of the passed file. The content returned by cat is stored in QUERY_TEXT variable
  5. The psql line connects to the given database name on localhost, executes the query and saves the returned rows to a csv file which will have same name as the name of the sql file. If you want to connect to some other database server, add following: 
    psql -h HOST_NAME_OR_IP -U db_user_name -d ...
edit this post