Saving an output of PostgreSQL query to a text file

When using PostgreSQL on Linux, there may be times that you wish to save the output of a query. Normally, the output appears on your screen. It’s possible to redirect this output to a file instead, which would allow you to view it later. In this guide, we’ll show you how to save the output of a PostgreSQL query to a file.

In this tutorial you will learn:

  • How to save the output of a PostgreSQL query to a file

Saving the output of a PostgreSQL query to text file

Saving the output of a PostgreSQL query to text file

Software Requirements and Linux Command Line Conventions
Category Requirements, Conventions or Software Version Used
System Any Linux distro
Software PostgreSQL
Other Privileged access to your Linux system as root or via the sudo command.
Conventions # – requires given linux commands to be executed with root privileges either directly as a root user or by use of sudo command
$ – requires given linux commands to be executed as a regular non-privileged user

Save PostgreSQL query output to file



  1. First, login to the PostgreSQL database. If your database is hosted locally and you’re already using the postgres user, then the following command will do the trick.
    $ psql
    
  2. Next, use the \o command to tell PostgreSQL where to save subsequent output. In this example, we’ll save our output to a file in /home/linuxconfig/output.txt but you can choose any file you want.
    postgres=# \o /home/linuxconfig/output.txt
    
  3. Now, execute any query or queries you want, and the output will be directed to the file you specified earlier.
    postgres=# select * from dummy_table;
    
  4. To revert this setting and have the output come onto the screen again, just type the \o command again.
    postgres=# \o
    
  5. We can now open the file to see the output has been saved there.
    $ cat /home/linuxconfig/output.txt
    
Saving the output of a PostgreSQL query to text file

Saving the output of a PostgreSQL query to text file



Closing Thoughts

In this guide, we saw how to save the output of a PostgreSQL query to a text file. This can be handy when you would rather save the output somewhere instead of having it appear on your screen. The option you need to remember is \o.



Comments and Discussions
Linux Forum