PostgreSQL: psql Commands Guide

PostgreSQL provides the psql utility to connect to and communicate with PostgreSQL databases. psql provides a command-line interface for connecting to a PostgreSQL instance. Every installation of the PostgreSQL server comes with psql, which is the default client for interacting with the server.

To work with PostgreSQL databases, psql offers a variety of commands. We will learn about these commands in this post.

Connect to Database

To connect to a database, psql offers a number of options. Below are the options.

psql [-d database name] [-U username] [-h hostname]
  1. database name: In database name, you can provide the database name you want to connect.
  2. username: user name
  3. host name: You can provide hostname in IPv4, IPv6 format or in string format

All the options are optional. If we don’t provide the above options and write only “psql” in the command prompt, then psql connects to the “postgres” database with the current operating system user on the local machine.

psql connect to database command

In the above image, I have connected to a demo database with postgres user. psql shows the current database name in the prompt.

If the user is a super user, then psql puts a # (hash sign) after the database name; otherwise, it puts a > (right arrow) sign.

psql Help Command

There are two ways to get help within the psql utility.

  1. \? : To get help on the psql commands.
  2. \h [searchtext]: To get help on the SQL statements

\? provides help with the basic psql commands. Below is an example:

psql help

To exit from the help, press “q.”.

\h is used to get help on the SQL statements. To get help with the particular statement, for example, ALTER TABLE, use “\h ALTER TABLE”.

psql alter table help

In the help, psql provides both command syntax and a description.

psql List Tables

To list all tables in the psql, use the \dt command. Below is an example.

psql \dt command to list all tables

List all Databases Command

To show all databases in a PostgreSQL server from the psql client, use the \l psql command.

\l psql command to list all databases.

psql Query

Every SQL statement in psql ends with a semicolon (;). Below is an example of a select query.

psql select query example

psql Basics Commands

Below are the basic commands in psql.

\dList tables, views and sequences
\d NAMEShow schema of tables, views, indexes, and sequences
\daList Aggregates
\db [PATTERN]List tablespaces
\dDList Domains
\ddpList default privileges
\dE [PATTERN]List foreign tables
\desList foreign servers
\detList foreign tables
\deuList user mappings
\dg or \duList Roles
\diList Indexes
\dlList large objects
\dnList schemas
\dtList only tables
\dTList data types
\dvList views
\dxList extensions
\lList databases
\sf funcnameShow afunction’s definition
\sv viewnameShow view’s definition
\conninfoDisplay current connection information
\h [name]show help of SQL commands
\iExecute commands from file
\o [file]Send all query results to file
\qquit psql
psql basic commands


PostgreSQL provides the psql command-line tool for connecting and executing SQL commands on PostgreSQL’s databases. It comes with every PostgreSQL installation. It offers various commands for database management.

Read More: