PostgreSQL: Basic psql Commands

Postgres supports numerous commands to perform various database operations. To execute such commands different interfaces are used. One such interface is “SQL Shell”, also known as, “psql”. Using psql, you can execute various commands to accomplish different database operations efficiently, such as accessing, creating, deleting, or updating a database, table, schema, etc.

This post presents a comprehensive understanding of basic “psql” commands through practical demonstration.

Contact us today for all your Postgres and Open Source consulting and support needs.

Introduction to Basic SQL Shell or psql Commands

The psql commands assist us in querying the data from the specified database interactively. Here are some of the most frequently used, most effective psql commands:

  • Connect to a Database: “psql -d db_name -U user_name”.
  • Check Postgres Version: “SELECT VERSION();”.
  • List All Databases: “\l”.
  • Access or Switch a Database: “\c db_name”.
  • List All Tables: “\dt”.
  • Describe All Tables: “\d”.
  • Describe a Specific Table: “\d tab_name”.
  • List All Schemas: “\dn”.
  • List All Views: “\dv”.
  • List All Functions: “\df”.
  • List All Users: “\du”.
  • Show Commands History: “\s
  • Save Query’s Results to a Specific File: “\o file_name”.
  • Run psql Commands/queries From a Particular File: “\i file_name”.
  • Execute Previous Command: “\g”.
  • Show Query Execution Time: “\timing”.
  • Get Output in HTML Format: “\H”.
  • Align Columns Output: “\a”.
  • Get Help: “\h”.
  • Get All psql Commands: “\?”.
  • Clear Screen: “\! cls”.
  • Quit psql: “\q”.

Let’s put these commands into practice to get a profound understanding.

Example 1: Connecting to a Database

Open the CMD and execute the below-provided psql command to establish a connection to a particular database:

psql -d postgres -U postgres

The output signifies that a connection with the “postgres” database has been successfully established under the user “postgres”.

Example 2: Checking Postgres Version

Executing the “SELECT VERSION();” command will retrieve the currently installed Postgres version:


The output shows that currently “PostgreSQL 15.1” is running on our system.

Example 3: Listing All Databases

Listing available databases is a very common task in Postgres that can be accomplished via the “\l” command:


The “\l” successfully retrieves the list of available databases.

Example 4: Accessing/Switching a Database

Performing any operation on a database object requires accessing that database. To accomplish this task, execute the “\c” command from the “SQL Shell”:

\c sample_db;

The connection with the “sample_db” database has been established successfully.

Example 5: Listing All Available Tables

In Postgres, the tables are used to represent the data elements in a well-organized format. Run the “\dt” command from SQL Shell to fetch the list of available tables/relations:


The stated command returns all the tables available in the selected database.

Example 6: Describing All Tables

Postgres users can use the “\d” command to get the list of relations, including sequences, views, etc.


The “\d” command successfully retrieves the “schema name”, “table name”, “relation type”, and owner.

Example 7: Describing a Specific Table

Execute the “\d” command followed by the table name to describe a specific table in Postgres:

\d emp_data;

The above-stated command will describe the “emp_data” table:


The stated command retrieves all the details regarding the “emp_data” table, such as column names, column types, columns’ default values, etc.

Example 8: Listing All Schemas

A Postgres schema is a namespace that keeps the database objects, such as relations, functions, etc. To fetch the list of schemas, use the “\dn” command:


The given command returns the names of all schemas along with their owners.

Example 9: Listing All Views

Views are a frequently utilized concept in Postgres that allows us to simplify complex queries. To show the list of views, use the “\dv” command:


The “\dv” command returns the view name, schema name, relation type, and view’s owner.

Example 10: Listing All Functions

In Postgres, the functions enhance code reusability, understandability, debugging, etc. To obtain the list of available functions, use the “\df”:


The “\df” command returns the “schema name”, “function name”, “result data types”, and “argument data types”.

Example 11: Listing All Users

In PostgreSQL, the users can have database privileges and can own the database objects, such as tables, schemas, etc. To get the user’s list, use the below command:


The “\du” command returns the “role name”, “attributes”, and “members details”.

Example 12: Show Command History

Open the terminal, log into “psql”, and execute the following command to see the query history:


The output shows that the “\s” command successfully retrieves the query history.

Example 13: Execute psql Commands From a Particular File

The SQL Shell supports a “\o” command that allows us to save query results to a specific file. Execute the “\o” command followed by the “file name”, as shown below:

\o 'C:/exeFile.txt';

The cursor moves to the next line, which proves that the “\o” command executes successfully. Now the output of the commands will be written to the “exeFile.txt” file until we execute the “\o” command:


Let’s open the desired file to see the result of the “\dt” command:


The output snippet verified the working of the “\o” command.

Example 14: Execute psql Commands From a Particular File

Postgres allows us to execute psql commands from a particular file using the “\i”. The sample file contains the following command:


Let’s utilize the “\i” command to execute the psql commands from the selected file:

\i 'C:/showtables.txt';

The output proves that the “\i” command successfully executes the commands from a specific file.

Example 14: Show Query Execution Time

In psql, the “\timing” command is used to enable or disable query execution time:


Let’s execute any command to see how the “\timing” command works:

SELECT * FROM emp_data;

Execute the “\timing” command one more time to disable the query execution time:


The output shows that the query execution has been “off”.

Example 16: Get Output in HTML Format

The “\H” command is used in psql to get the command’s output in HTML format:


Now the output of any particular command will be displayed in HTML format as follows:

SELECT * FROM emp_data;

To disable the HTML format, use the “\H” command one more time.

Example 17: Execute Previous Command

Use the “\g” command to run the previously executed command:


The “\g” command retrieves the result based on the previously executed command.

Example 18: Align Columns Output

Execute the “\a” command to align or unaligned the output format:


The output snippet shows that the output format is “unaligned”. Run any Postgres-supported command to understand this concept better:

SELECT * FROM emp_data;

The output proves that the result set is unaligned.

Example 19: Get Help

Use the “\h” command to get help regarding any command or query. For instance, the below command will provide help regarding the “INSERT INTO” command:


The “\h” command retrieves the details regarding the “INSERT” command.

Example 20: Get All psql Commands

Execute the “\?” command to get all available psql commands:


The output displays all available commands in psql.

Example 21: Clear Screen

Execute the “\! cls” command from psql to clear the screen:

\! cls

Hitting the enter button will clear the screen.

Example 22: Quit psql

The “\q” command is used to quit or exit the SQL Shell (psql):


That’s all! We have discussed various basic yet very important psql commands.


psql is a command-line interface used to perform various database tasks efficiently. For instance, using psql different commands can be executed to access, create, delete, or update a database, table, schema, etc. Moreover, using psql, you can store the output of commands to a specific file or execute the commands from a particular file. In this write-up, we have discussed the most frequently used psql commands with practical demonstration.