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.