Photo by Tom Pumford on Unsplash
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:
$ ~/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:
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:
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:
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:
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:
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.
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.
A Call To Action!
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.
Have I mentioned how much I love a cup of coffee?!?!
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!
Be sure and visit the “Best Of” page for a collection of my best blog posts.
Josh Otwell has a passion to study and grow as a SQL Developer and blogger. Other favorite activities find him with his nose buried in a good book, article, or the Linux command line. Among those, he shares a love of tabletop RPG games, reading fantasy novels, and spending time with his wife and two daughters.
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). The majority, if not all, of the examples provided are performed on a personal development/learning workstation-environment and should not be considered production quality or ready. Your particular goals and needs may vary. Use those practices that best benefit your needs and goals. Opinions are my own.
One thought on “Utility psql command for saving query output to a file.”