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.


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:

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


Utility psql command for saving query output to a file.


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.