Postgres Slash Commands
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.
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 thepsql
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.
\a
— Displays data either inunaligned
mode oraligned
mode, of which the latter is more easily readable.
\t
— Stands forTuples
. 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.
\!
— From withinpsql
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 topsql
. 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 forquit
and allows the user to leave thepsql
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 ofSELECT * FROM some_table;
inputSELECT * 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.
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.
Pilot the ObjectRocket Platform Free!
Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.
Get Started