Meta commands are the instructions given to the client engine/psql by the user to display some functionalities or perform certain operations. These commands are also called the “slash or backslash command” because they begin with a backslash followed by some command verb. Arguments can also be specified in the meta-commands. The SQL Shell (psql) supports a long list of meta-commands.
This tutorial will demonstrate the most frequently used meta-commands in psql.
A Comprehensive Guide on psql Meta-Commands
The meta-commands are small commands written/prefixed with the backslash. These commands are used to perform different operations like managing users, databases, tables, etc. There is a long list of meta-commands provided by the psql and a few of them are listed below:
- \l - Lists All the Databases
- \c<database_name> - Connect to a Specific Database
- \d - Display Objects
- \x - Set the Expanded Display
- \set - Display Internal Variables
- \echo<text> - Print Message
- \? - get help
- \a - Toggles Alignment of the Output Format
- \C<caption> - Sets the Title/Caption of a Table
- \r - Resets the Query Buffer
- \z - Lists All the Tables With Their Revoked Permissions
- \q - Quit psql
We will go over them one by one with the implementation.
\l - Lists All the Databases
The \l meta-command lists all the databases in the system. Let's see how it works:
\l
Important: The \l command must be executed without a semi-colon; otherwise, you wouldn't get the desired results.
\c<database_name> - Connect to a Specific Database
The \c<database_name> command is used to get connected to the database whose name is specified in the command:
\c test_db
The output demonstrates that running the "\c" command connects us to the specified database, i.e., "test_db".
\d - Display Objects
The \d meta-command displays all the objects. These objects include a table, view, index, schema, and database. We will implement the \d command with different options to display different database objects.
- \dt - Display Tables
This meta-command displays all the tables in the database when it is used as “\dt”:
\dt
The above enlisted are the tables available in my default database.
- \dv - Display Views
This meta-command will give us all the views present in our database. In my case, I have never created any view so the command will find no such relation. The \dm is used to display the materialized view. The same will be the output for this case:
\dv; \dm;
- \di - Display Indexes
To display indexes, we use the \di command as follows:
\di
- \dn - Display Schemas
The “\dn” command will give the list of schemas present in the system:
\dn
- \dT - Display the Data Types
The \dT command displays the user-defined data types:
\dT
Note that the “\dt” is different from “\dT”. The \dt displays tables.
- \ds - Display the Sequences
The “\ds” command displays all the sequences of a database.
- \dtS - Displays System Catalog Tables
The system catalog tables consist of the tables and views and describe the structure of the database. These can be displayed in the psql command line using the \dtS command:
\dtS
- \du - Displays all the Roles in the Database
This meta-command gives all the user roles defined in the system:
\du
In my case, the only user role defined is the Postgres.
\x - Set the Expanded Display
We can toggle the expanded display using the "\x" command. This is useful for large tables. We can set it to auto or can be toggled on or off:
\x
\set - Display Internal Variables
The \set command is used to list all the internal variables, as shown below:
\set
\unset <name_of_variable>- Delete an Internal Variable
This command will delete the internal variable from the list.
\echo<text> - Print Message
This command is used to get the text/message printed to the console:
\echo Hello From Command Prompt!
\? - get help
This command is used to get help from the psql, particularly regarding meta-commands:
\?
\a - Toggles Alignment of the Output Format
The “\a” command aligns and unaligns the output format, as demonstrated in the following snippet:
\a
\C<caption> - Sets the Title/Caption of a Table
The “\C<caption>” command sets the title for the database table:
\C hello
\r - Resets the Query Buffer
This command resets the query buffer:
\r
\z - Lists All the Tables With Their Revoked Permissions
The “\z” command displays the list of tables with their objects as Access Control Lists i.e. ACLs:
\z
\q - Quit psql
By running the “\q” query you can quit the psql, as illustrated in the following snippet:
\q
Now by pressing any key, you can simply exit from the psql.
Additional Information
You can execute multiple meta-commands at the same time to perform the desired operation like this:
In the above example, we have used three commands to display the table caption, and the table, and toggle the alignment respectively and simultaneously.
We can add a “+” sign to every command to get some additional technical information about whatever is the output. For example; running the “\dt” gives:
While if we execute the “\dt+” command. There is some additional information displayed.
That's all the essential details regarding the psql meta-commands.
Conclusion
The meta-commands are the commands offered by psql to perform certain operations. These commands start with the backslash and are executable only in psql. In this post, we have seen many meta-commands that are being used widely.
