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.

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:

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”.

\h ALTER TABLE

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.

\dt

List all Databases Command

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

\l

psql Query

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

psql Basics Commands

Below are the basic commands in psql.

Command	Description
\d	List tables, views and sequences
\d NAME	Show schema of tables, views, indexes, and sequences
\da	List Aggregates
\db [PATTERN]	List tablespaces
\dD	List Domains
\ddp	List default privileges
\dE [PATTERN]	List foreign tables
\des	List foreign servers
\det	List foreign tables
\deu	List user mappings
\dg or \du	List Roles
\di	List Indexes
\dl	List large objects
\dn	List schemas
\dt	List only tables
\dT	List data types
\dv	List views
\dx	List extensions
\l	List databases
\sf funcname	Show afunction’s definition
\sv viewname	Show view’s definition
\conninfo	Display current connection information
\h [name]	show help of SQL commands
\i	Execute commands from file
\o [file]	Send all query results to file
\q	quit psql

Summary

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:

• PostgreSQL Data Types and Sizes with Examples
• PostgreSQL Current Timestamp