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
- We need to send command line arguments to our shell script. The index of command line starts with $0, $1 …
- $0 is the name of script which is export-data.sh, $1 is the name of sql file we want to run
- Hence, we made sure that $1 should not be empty. If it is not provided, we printed how to run the program
- 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
- 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 ...