Everyone likes a shortcut. So long as it doesn’t backfire. Cutting corners and some shortcuts are a bad idea. But I’m talking about a handy, productive shortcut. Now those I can really dig. Keep reading to know a great shortcut and add even more ‘chops’ to your psql command-line-fu.
Note: All data, names or naming found within the database presented in this post, are strictly used for practice, learning, instruction, and testing purposes.
OS and Database:
- Xubuntu Linux 16.04.3 LTS (Xenial Xerus)
- PostgreSQL 10.4
Major salute, applause, ode (however you want to term it) to the epic and powerful ‘wizards spell book’, PostgreSQL Up and Running, by Regina Obe and Leo Hsu. This tome of might help spark the idea for this blog post, along with many others.
If you don’t have this book, what on earth are you waiting for?
Get it!!
I have this table that tracks my trail hiking stats:
1 2 3 4 5 6 7 8 9 10 11 12 | fitness=> \d hiking_stats; Table "public.hiking_stats" Column |Type | hike_id |integer | day_walked |date | cal_burned |numeric(4,1) | miles_walked |numeric(4,2) | duration |time without time zone| mph |numeric(2,1) | additional_weight|boolean | trekking_poles |boolean | shoe_id |integer | |
I’ll imagine that my stats are automatically uploaded (they aren’t) and that I always query for the current day I walked, along with the cal_burned column:
1 2 3 4 5 6 7 | fitness=> SELECT day_walked, cal_burned fitness-> FROM hiking_stats fitness-> WHERE day_walked = current_date; day_walked | cal_burned ------------+------------ 2018-05-24 | 396.3 (1 row) |
Also, I look at the previous days stats, just to compare (and berate myself haha):
1 2 3 4 5 6 7 | fitness=> SELECT day_walked, cal_burned fitness-> FROM hiking_stats fitness-> WHERE day_walked = current_date - 1; day_walked | cal_burned ------------+------------ 2018-05-23 | 397.9 (1 row) |
I could save these queries in some sort of VIEW and just query it when I am curious.
But since I’m in a psql session so much anyways, why not have a ‘shortcut’ for them each.
But how?
With the psql \set meta-command.
Check this out.
1 2 3 | fitness=> \set todays_stats 'SELECT day_walked, cal_burned fitness-> FROM hiking_stats fitness-> WHERE day_walked = current_date;' |
1 2 3 | fitness=> \set yest_stats 'SELECT day_walked, cal_burned fitness-> FROM hiking_stats fitness-> WHERE day_walked = current_date - 1'; |
Then call each varible in psql:
1 2 3 4 5 | fitness=> :todays_stats day_walked | cal_burned ------------+------------ 2018-05-24 | 396.3 (1 row) |
1 2 3 4 5 | fitness=> :yest_stats day_walked | cal_burned ------------+------------ 2018-05-23 | 397.9 (1 row) |
Sweet deal!!!
How cool would it be to have that capability every day when I am in a psql session and just so happen to be curious about my hiking stats?
Perhaps I can set these shortcuts in my psqlrc file?
I’ll save something like that for another blog post on another day and in the meantime, craft up a simple .psql script to set them.
First let’s create that file in a directory where I keep such things:
1 | fitness=> \! touch ~/My_Scripts/daily_hike_stats.psql |
Did you like that handy use of the \! meta-command?
Want to learn more about it?
Interested in other useful psql meta-commands?
Then, make sure and check out the linked articles towards the end of the post for some psql ninja goodies!
Shameless plug aside, let’s see what I’ve added into the daily_hike_stats.psql file:
1 2 3 4 5 6 7 8 9 10 | fitness=> \! cat ~/My_Scripts/daily_hike_stats.psql \c fitness \set todays_stats 'SELECT day_walked, cal_burned FROM hiking_stats WHERE day_walked = current_date;' \echo 'todays_stats variable is set for use' \set yest_stats 'SELECT day_walked, cal_burned FROM hiking_stats WHERE day_walked = current_date - 1;' \echo 'yest_stats variable is set for use' |
I threw in a \echo meta-command for a nice message letting me know the variables have been set. You know, in case I forget once I log into the fitness database.
I’ll log out and back in (not shown) then try to access the variables:
1 2 3 4 5 6 7 8 | fitness=> :todays_stats; ERROR: syntax error at or near ":" LINE 1: :todays_stats; ^ fitness=> :yest_stats; ERROR: syntax error at or near ":" LINE 1: :yest_stats; ^ |
Since I logged out, they are no longer set.
No worries, let’s call the daily_hike_stats.psql file:
1 2 3 4 5 | fitness=> \i ~/My_Scripts/daily_hike_stats.psql Password for user my_user_name: You are now connected to database "fitness" as user "my_user_name". todays_stats variable is set for use yest_stats variable is set for use |
According to the messages, the variables have been set.
Let’s check them:
1 2 3 4 5 6 7 8 9 10 11 | fitness=> :todays_stats; day_walked | cal_burned ------------+------------ 2018-05-24 | 396.3 (1 row) fitness=> :yest_stats; day_walked | cal_burned ------------+------------ 2018-05-23 | 397.9 (1 row) |
Boom!
Worked perfectly!
Without having to query for the data ad-hoc.
Win win!
Before you go, check out these other handy psql meta-command blog posts I have written. I know you will like them as well:
- Utility psql command for saving query output to a file.
- Two handy examples of the psql \copy meta-command
- 3 command knockout combo for your psql command-line session
- Shell excitement with the psql \! meta-command
Now you know how to utilize the \set meta-command for setting a ‘shortcut’ variable in psql. Get you to setting some of your own and enjoy another great psql feature.
Feel free to leave comments below.
Be sure and visit the stellar on-line PostgreSQL 10 Documentation for any related questions.
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.
Visit the Portfolio-Projects page to see blog post/technical writing I have completed for clients.
Have I mentioned how much I love a cup of coffee?!?!
To receive email notifications (Never spam) from this blog (“Digital Owl’s Prose”) for the latest blog posts as they are published, please subscribe (of your own volition) by clicking the ‘Click To Subscribe!’ button in the sidebar on the homepage! (Feel free at any time to review the Digital Owl’s Prose Privacy Policy Page for any questions you may have about: email updates, opt-in, opt-out, contact forms, etc…)
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.