Common PostgreSQL Meta-Commands

PostgreSQL meta-commands are terse commands preceded by a backslash which make common tasks very easy. For a complete list of Meta-Commands, download your version of the PostgreSQL Documentation Manual and search for “MetaCommands” in the table of context. Here we’re going to look at the meta-commands that perform tasks we are most likely to use every day.

List all Databases

A good place to start upon logging in to PostgreSQL (unless we specified a database with -d) is to view a list of our databases.

postgres=# \l

To Connect to a Database

We’ll start with connecting to a database.

postgres=# \c or \connect [ dbname [ username ] [ host ] [ port ] ]

Display Object Information

The \d meta-command is pretty sweet in that it spits out information on most any object you throw at it. And \d+ provides additional info depending on the object type.  By itself \d will display the objects in a database. Our janglesdb database has a table and a sequence in it.

postgres=# \d

List Users and Roles

postgres=# \dg

Change Output Options

PostgreSQL supports several types of output and customizing how the output is formatted

postgres=# \a  — switch from aligned to non-aligned column output
postgres=# \H — output to HTML

Investigate \pset for modifying output styles.

Display Command History

It’s often helpful to display your command history. There is no !527 to execute a command by number like in bash, but still valuable.  Add a filename if you want the history saved to a file, otherwise your command history will print in the console.

postgres=# \s [filename]

Using Psql Variables

Psql Variables can be very useful in a psql session.

postgres=# \set [ name [ value [ … ] ] ] — sets a psql variable
postgres=# \set  — displays variables
postgres=# \unset — removes psql variable from memory
postgres=# \echo :name — displays a variable (variables are preceded by colon)

Execute Previous Psql Command

This saves some typing!

postgres=# \g

You can also send the output of the previous command to a file or even pipe it to another command. Very handy.

postgres=# \g  \w filename (output to file)   \w | commmand (to pipe to command)

Execute Psql from File

This is a very handy meta-command. Reads input from the file filename and executes it as though it had been typed on the keyboard.

postgres=# \i filename

If you ever want to change your directory use the following:

postgres=# \! pwd — “\!” executes a shell command
postgres=# \cd /your/directory — change directory

We’re just scratching the surface with psql meta-commands in this post. Most of these meta-commands have additional capabilities not mentioned here, and of course, there are a ton of meta-commands not listed. Download the Psql Documentation Manual for the complete guide.