PostgreSQL is one of the most used open source database solutions besides MariaDB / MySQL. Even though they have some similarities, PostgreSQL is based on a object-relational database management system while MariaDB and MySQL are relational database management systems.
This article is about how to save a PostgreSQL output. While you can use the command mysqldump to dump your MariaDB or MySQL database, saving an output with PostgreSQL is a little bit different and doesn’t require an extra tool.
Switch to the database
First and foremost we have to change / go to the database. In Linux you also have to be the PostgreSQL system user in order to work with the database. To change the user just use sudo:
user@machine:~$ sudo -u postgres /bin/bash
You are now the user postgres. You can see this at the beginning of your command line. Instead of your username you can now see postgres@ at the beginning of the line. As next we use the psql command to switch into the actual database we want to dump:
postgres@machine:~$ psql database psql (9.3.14) Type "help" for help.
You are now connected to the database. Don’t forget to replace database with the desired database you want to connect to.
Save a PostgreSQL output
To finally save an output you can use normal SQL statements which are returning your desired value and wrap this command within a COPY statement. The following example saves the output of an PostgreSQL statement in the file output.txt which will be located in /tmp:
database=# COPY (select firstname, lastname from user_table) TO '/tmp/output.txt';
Save a PostgreSQL output as CSV
You can also define in which format the output should be saved. You can save the output as a CSV file so that you will be able to easily work with the output within LibreOffice or MS Office for example. The following example saves the same output as in the example above, but uses CSV with semicolons as a format:
database=# COPY (select firstname, lastname from user_table) TO '/tmp/ouput.csv' (format csv, delimiter ';');
Exit database / psql command
To exit the database access and therefore the running psql command, simply enter \q to quit.
As you can see, saving / dumping PostgreSQL output directly into a text file even as a CSV file is a great feature. No extra tools needed. But PostgreSQL comes with much more than just dumping output to test and CSV files. Even the COPY command can be modified with a lot of other different settings. A good book about PostgreSQL is PostgreSQL: Up and running by Regina Obe and Leo Hsu. It covers a lot of other things besides dumping output from PostgreSQL as well.