Postgres Slash Commands

Written by Data Pilot
November 13, 2019
  • PostgreSQL
Have a Database Problem? Speak with an Expert for Free
Get Started >>

Introduction to the “slash” Postgres commands

Complex queries take more time to code. As a result, the longer the query, the greater chance there is of making a coding error. This is where Postgres slash commands (\) can help . They assist developers in simplifying their PostgreSQL statements. Among other things, backslash commands also enable users to navigate through tables in a database, list them, and display results in different ways. Find out more in this overview. Take a few moments to explore the many options of using backslash commands now.

Prerequisites to using Postgres commands

  • PostgresSQL – Install it, and then check the version with the command psql -V to verify it was installed correctly.

Screenshot of a terminal window getting the PostgreSQL server status and psql version

Accessing PostgreSQL using the ‘psql’ command line interface

  • At the command line on your localhost server, input the command psql to connect the PostgreSQL database you’ve created for this examples in this overview of Postgres commands:
1
psql postgres
  • Alternatively, to connect, use the -U, _h, and _d flag parameters to input the username, IP address or host domain, and name of the database. When using a single parameter, it will be understood as being the name of the database.
1
psql -U some_username -h 127.0.0.1 -d some_database

PostgreSQL slash commands in psql

There are many backslash (\) commands available, too numerous to list here. However, this article will show you some of the more popular commands. You’ll notice that some backslash commands reduce longer SQL statements. Other Postgres commands help you maneuver through a database table or change how it is displayed.

NOTE: To exit a command or display of results, press CTRL+C.

Psql slash commands for user accessibility and convenience

  • \? – Finds facts for you about the psql commands.

  • \h [SQL_KEYWORD] – Shows a topics list for a command based on the keyword you enter. For instance, type \h CREATE TABLE and it will return helpful information about that keyword.

Screenshot of psql using the \h PostgreSQL help command to return info about CREATE TABLE SQL keyword

  • \a — Displays data either in unaligned mode or aligned mode, of which the latter is more easily readable.

Screenshot of psql returning Postgres table data as aligned and unaligned

  • \t — Stands for Tuples. It’s either set to On or Off. When on, rows are returned, not names of a column.

  • \s — History of SQL commands used. Save the file to have a list of carried out commands in the current directory. For example: \s my_commands.txt.

  • \z — Lists related schemas, tables in a database, and identifies the users given privileges to use the tables.

Screenshot of the tables and user privileges fora PostgreSQL database in psql

  • \! — From within psql for executing a command using a command prompt or UNIX command. Must be connected to a database in PostgreSQL. An example is if you’re using a UNIX terminal and you’re connected to psql. Enter \! clear to erase the screen data.

  • \i — For statement executing commands located in a file .sql like this: \i my_commands.sql, which shows the name of the file.

  • \q — Stands for quit and allows the user to leave the psql interface for the command line.

Psql slash commands for SQL queries and statements

Use these backslash (\) Postgres commands to assist you with tables and database management.

  • \g — Stop or start an PostgreSQL statement with \g as opposed to a semicolon. In place of SELECT * FROM some_table; input SELECT * FROM some_table\g.

  • \c [DATABASE_NAME] — Make a different database connection with the \c command in lowercase.

  • \C [TABLE_NAME] — Get the title name of the table in Postgres with the \C command in uppercase.

  • \d [TABLE_name] — Obtain the details of a table in Postgress such as the data types and columns with the \d command.

  • \d – Returns every SQL database table name or related table listings.

  • \du – Lists either role attributes or role privileges for each role in Postgres.

Screenshot of the \c and \d commands in a psql terminal window

List the PostgreSQL roles in psql

  • \du – Returns a list or group or user roles and associated privileges for those roles. “List of roles” follows:
1
2
3
4
5
6
List OF roles
ROLE name | Attributes | Member OF
-----------+------------------------------------------------------------+-----------
postgres | Superuser, CREATE ROLE, CREATE DB, Replication, Bypass RLS | {}
some_user | Cannot login +| {}
| Password valid until 2099-01-25 00:00:00+08 |

Other commands used and works in the PostgreSQL database cluster

  • \dn – to return a Postgres database schema list.
1
2
3
4
5
6
postgres=# \dn
List OF schemas
Name | Owner
--------+----------
public | postgres
(1 ROW)

  • \df – To see the stored functions and procedures. The code is \df function_name to see the function.

  • \dv – For a view listing.

1
2
3
4
5
List OF relations
Schema | Name | TYPE | Owner
--------+------+------+----------
public | v | VIEW | postgres
(1 ROW)
  • \l – View a database listing.
1
2
3
4
5
6
7
8
9
10
List OF DATABASES
Name | Owner | Encoding | COLLATE | Ctype | Access privileges
---------------+----------+----------+-------------+-------------+-----------------------
postgres | postgres | UTF8 | en_PH.UTF-8 | en_PH.UTF-8 |
some_db | postgres | UTF8 | en_PH.UTF-8 | en_PH.UTF-8 |
template0 | postgres | UTF8 | en_PH.UTF-8 | en_PH.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_PH.UTF-8 | en_PH.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
(4 ROWS)
  • \dt – To get the database table listing.
1
2
3
4
5
6
7
some_db=# \dt
List OF relations
Schema | Name | TYPE | Owner
--------+----------+-------+----------
public | employee | TABLE | postgres
public | users | TABLE | postgres
(2 ROWS)

  • \dt+ – To get additional information, add a plus sign to the backslash \dt+ command.

  • \d table_name – This shows the details for the table

  • \dy – For a trigger events list.

1
2
3
4
5
some_db=# \dy
List OF event triggers
Name | Event | Owner | Enabled | PROCEDURE | Tags
------+-------+-------+---------+-----------+------
(0 ROWS)
  • \di – To get the index list.
1
2
3
4
5
6
some_db=# \di
List OF relations
Schema | Name | TYPE | Owner | TABLE
--------+------------+-------+----------+-------
public | users_pkey | INDEX | postgres | users
(1 ROW)
  • \x – To show a query in a stylistic format where the content is easier for the user to read.

Conclusion to Postgres commands for SQL

Backslash (\) Postgres commands make database table queries easier to manage. The variety of views that are attainable in a result set allows users to make meaningful queries that get straight to the point. By selecting the right Postgres command, you’re able to do so much more in less time. Keep this article as a handy reference and revamp your coding methodology for the psql command-line interface today.

Rate 1 Star2 Stars3 Stars4 Stars5 Stars

Pilot the ObjectRocket Platform Free!

Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.

Get Started
Related Topics:
Additional Information:

Keep in the know!

Subscribe to our emails and we’ll let you know what’s going on at ObjectRocket. We hate spam and make it easy to unsubscribe.