Utility psql command for saving query output to a file.

Ever needed or wanted to save particular query results for later? Perhaps you need to really study something for better understanding. Scrutinize a dataset with a hawk’s eye while having a coffee? Or just list out all of the tables in the database you are working in? The psql \o meta-command is your new best friend.

Note: All data, names or naming found within the database presented in this post, are strictly used for practice, learning, instruction, and testing purposes. It by no means depicts actual data belonging to or being used by any party or organization.

I will be using Xubuntu Linux 16.04.2 LTS (Xenial Xerus) and PostgreSQL 10.0 for these exercises.


To start, I create an empty text file in my working directory with the touch command:
bigdaddy@LE2:~/Practice_Data/Psql_meta_commands$ touch slash_o.txt
Now, using the psql meta-command,\o we can arrange for future query results to be written to the provided filename.
learning=> \o ~/Practice_Data/Psql_meta_commands/slash_o.txt
Displayed above, we can see one variation of the easy syntax structure is:
\o filename
For this example, I will use a generate_series() Set Returning Function to generate rows of generic data to illustrate a quick example of JOIN syntax for the purpose of this blog post.
Perhaps a nice visual representation of the query results of the differences between an INNER JOIN a RIGHT JOIN and a LEFT JOIN saved to a file will help solidify understanding.


First, we run the below LEFT JOIN query:

learning=>SELECT gs_col1.gs_col1, gs_col2.gs_col2
FROM generate_series(1,10)gs_col1
LEFT JOIN generate_series(5,15)gs_col2
ON gs_col1.gs_col1 = gs_col2.gs_col2;

Going to the directory where the slash_o.txt file is located, we can cat out, and see, the contents of this file with:

bigdaddy@LE2:~/Practice_Data/Psql_meta_commands$ cat slash_o.txt
 gs_col1 | gs_col2
---------+---------
       1 |        
       2 |        
       3 |        
       4 |        
       5 |       5
       6 |       6
       7 |       7
       8 |       8
       9 |       9
      10 |      10
(10 rows)

Let’s try using an INNER JOIN  next, with the same query and see the difference in the results, which will be appended to the same slash_o.txt file:

learning=> SELECT gs_col1.gs_col1, gs_col2.gs_col2
FROM generate_series(1,10)gs_col1
INNER JOIN generate_series(5,15)gs_col2
ON gs_col1.gs_col1 = gs_col2.gs_col2;

Looking at the file now shows a difference between the LEFT JOIN and INNER JOIN:

bigdaddy@LE2:~/Practice_Data/Psql_meta_commands$ cat slash_o.txt
 gs_col1 | gs_col2
---------+---------
       1 |        
       2 |        
       3 |        
       4 |        
       5 |       5
       6 |       6
       7 |       7
       8 |       8
       9 |       9
      10 |      10
(10 rows)

 gs_col1 | gs_col2
---------+---------
       5 |       5
       6 |       6
       7 |       7
       8 |       8
       9 |       9
      10 |      10
(6 rows)

Finally, we come full circle with a RIGHT JOIN query:

learning=> SELECT gs_col1.gs_col1, gs_col2.gs_col2
FROM generate_series(1,10)gs_col1
RIGHT JOIN generate_series(5,15)gs_col2
ON gs_col1.gs_col1 = gs_col2.gs_col2;

Checking the files’ contents now shows the appended query output from the RIGHT JOIN as well.

bigdaddy@LE2:~/Practice_Data/Psql_meta_commands$ cat slash_o.txt
 gs_col1 | gs_col2
---------+---------
       1 |        
       2 |        
       3 |        
       4 |        
       5 |       5
       6 |       6
       7 |       7
       8 |       8
       9 |       9
      10 |      10
(10 rows)

 gs_col1 | gs_col2
---------+---------
       5 |       5
       6 |       6
       7 |       7
       8 |       8
       9 |       9
      10 |      10
(6 rows)

 gs_col1 | gs_col2
---------+---------
       5 |       5
       6 |       6
       7 |       7
       8 |       8
       9 |       9
      10 |      10
         |      11
         |      12
         |      13
         |      14
         |      15
(11 rows)

Since the \o meta-command is still active in the query buffer, you can clear it out, by issuing another \o meta-command, and continue on with normal activity, without writing anything else to the specified file.

With this simple example, you can see how utilizing the psql meta-command, \o will allow you to keep some interesting query results for future perusal.


Thank you for taking the time to read this post. I truly hope you discovered something interesting and enlightening. Please share your findings here, with someone else you know who would get the same value out of it as well.

To receive notifications for the latest post from “Digital Owl’s Prose” via email, please subscribe by clicking the ‘Click To Subscribe!’ button in the sidebar!


Disclaimer: The examples presented in this post are hypothetical ideas of how to achieve similar types of results. They are not the utmost best solution(s). Your particular goals and needs may vary. Use those practices that best benefit your needs and goals. Opinions are my own.

Leave a Reply