psql meta-commands

Hacking SQL – Tools, Utilities, and Work flow.

I recently got a great suggestion from a reader over on Medium about sharing my workflow tools when hacking on SQL (mainly PostgreSQL, but sometimes MySQL also), hence here we are in this blog post.

(more…)

Use shortcuts in psql to make your life easier with the \set meta-command.

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.

dan-gold-327078-unsplash.jpg

Photo by Dan Gold on Unsplash

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:

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.

Shell excitement with the \! psql meta-command.

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 \!?

(more…)

3 command knockout combo for your psql command line session.


This blog post will discuss my favorite, top 3 psql meta-commands. Want to kick your command line session in the rear?? Try these out yourself.

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.

pankaj-patel-515219

Photo by Pankaj Patel on Unsplash

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

Cut To The Chase

I am listing these out in order from bottom-most to top.
Here we go.

  • Number 3: \help. Honestly, I use this command more than any other. It’s so nice not having to leave the terminal for a web page or document when I have a question about a SQL commands’ syntax. Here is a couple of example uses.
    • Issue \help to see a list of available commands
    • Question about ALTER TABLE? No problem. Run \help ALTER TABLE and boom. The commands’ syntax is returned to your terminal. (I visit this one often)
  • Number 2: \i – So handy! Use this meta-command to read the contents of a file and execute on the command line.
    Here’s an example.
    Suppose you have a .sql file containing this SQL statement:

    1
    2
    3
    SELECT first_name, last_name
    FROM friends
    ORDER BY first_name ASC;

    To run on the command line, simply prefix \i to the file path:

    \i /path/to/my/file/to/execute/

    Presto!! The contents are executed. Works well for files composed of long SQL statements and commands.

  • Number 1: Drum roll… By far my absolute top psql meta-command goes to none other than…. \e!!!
    Why the long face?
    What were you expecting?
    Are you using \e?
    What!?!?
    Come on!!!!
    So, what’s the magic?
    \e lets you use the defined system editor (Uh, Vim… Duh.) to edit the current query buffer contents or a file.
    This meta-command is such a killer feature! I abuse those two characters on my keyboard, daily, using this meta-command.Let’s see some examples.

    • To edit the current query buffer, simply issue \e on the command line. You are dropped into a temporary file, opened with the defined editor, with query buffer contents present. So cool!
    • Want to edit that previous .sql file we just used? Pass it in for editing. Like so:
      \e /my/file/to/edit

      Remember this, once completed and exited, any SQL queries ending with a semicolon are executed. This applies to either the file or query buffer.

Similar Reading.

Have a look at these blog post I have written on other psql meta-commands.

Tell Me Yours!!

I hope this blog post introduced you to these 3 psql meta-commands. But please! By all means. Tell me what are your favorites! I would love to hear from you and learn those you are fond of and use most.

As always, visit the impeccable On-line PostgreSQL Documentation for further study and information.

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 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.

Two handy examples of the psql \copy meta-command.

In this blog post, I will cover example uses of the psql \copy meta-command, for client-side work with the PostgreSQL database server. This command allows users who do not have Superuser privileges, to perform COPY type SQL commands.

(more…)