Excitement! Everyone loves excitement. After reading this blog post, you will know how to avoid exiting your current psql terminal session to another terminal window for shell-type task. Are you excited? I am! Or should I say, I am
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.3 LTS (Xenial Xerus) and PostgreSQL 10.3 for these exercises.
Let’s Get to Work
Ever had to escape to another terminal window for tasks on the command-line, while working within your psql session?
Thinking of switching to another terminal window right now for something, aren’t you?
Open up another terminal window?
Switch screens? Boring…
Shell tasks getting the best of you???
PostgreSQL’s psql meta-command
\! will halt you in your tracks!
Suppose I have a directory that stores database backups and I want to know when the last backup was.
Let me open another shell and change into that directory and look…
Smack!!!!(***Proverbial hand slap***)
Check this out.
We can go about this in one of two ways.
First, with no arguments,
\! drops you into the OS shell.
current_user:~$ ls -a PG_dumps/
. .. 2018_02_26_PG_back.gz aw_tble_dump.sql
While in the sub-shell, you can carry-out your commands. When finished, use
CTRL-D and boom, right back to your psql session.
Like that don’t you?!?
The second option, just pass the shell commands you want to execute immediately after
. .. 2018_02_26_PG_back.gz aw_tble_dump.sql
Upon completing the command(s), you are immediately returned to your psql session prompt. How cool is that!!!
Are you excited yet!?!
Here is valuable information taken directly from the exceptional PostgreSQL documentation (link in closing section below) about
“Unlike most other meta-commands, the entire remainder of the line is always taken to be the argument(s) of \!, and neither variable interpolation nor backquote expansion are performed in the arguments. The rest of the line is simply passed literally to the shell.”
How about we make a copy of one of those backup files using
\! and avoid the old way of switching to another terminal?
Then, let’s verify the new file is there by listing out the contents of that directory, all the while staying in our current session:
. .. 2018_02_26_PG_back.gz aw_tble_dump2.sql aw_tble_dump.sql
How much easier is that as opposed to opening another terminal window session?
Shades easier huh?
Other Handy psql meta-commands
Like what you’ve just read?
Interested in other handy, go-to psql meta-commands?
Then check out the blog posts I have written below and many thanks!!!
- 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
Try It Yourself.
I hope through this short blog post, your mind is already churning out ideas of how to incorporate
\! into your psql session. Check out the PostgreSQL 10 Documentation for a deeper look.
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.
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.