Skip to main content
HomeAbout SQLLearn SQL

10 Command-line Utilities in PostgreSQL

In this tutorial, learn about 10 handy command-line utilities in PostgreSQL which can enable you to interact with databases efficiently.
Mar 2019  · 7 min read

PostgreSQL is one of the most well accepted open-source RDBMSs out there. The default installation of PostgreSQL comes with a psql prompt which is the terminal-like interface of PostgreSQL. pgAdmin, on the other hand, provides a graphical interface for operating with databases in PostgreSQL. With psql, apart from executing SQL queries, you get more functionalities and some of which you will be learning in this tutorial.

You will start by learning how to retrieve the list of databases residing at a PostgreSQL server and slowly, you will proceed towards more intermediate stuff. Let's begin.

Note: This tutorial assumes that you are already familiar with SQL (which is a general language for interacting with RDBMSs) and PostgreSQL. Feel free to refresh the basics with the following resources:

First Things First

You might want to have an already well-formed database for following along with this tutorial. You can use this backup file and restore it in your PostgreSQL server. If you want to know how to do this, you can read this article.

In case, you are wondering what psql is; this excerpt from the official site of PostgreSQL talks about psql in an elegant way-

psql is a terminal-based front-end to PostgreSQL. It enables you to type in queries interactively, issue them to PostgreSQL, and see the query results. Alternatively, input can be from a file or from command line arguments. In addition, psql provides a number of meta-commands and various shell-like features to facilitate writing scripts and automating a wide variety of tasks.

As mentioned, you will be learning the ten most useful psql commands in this tutorial. Before you proceed, make sure you have PostgreSQL installed (preferably a version >= 9.4). This article will help you get PostgreSQL installed.

The next step is to get to this prompt:


On Windows, you can just type psql on the Start Menu Search Bar, and you should be able to navigate to it. This is referred to as the psql shell which gives you the psql prompt. As you can see, the prompt is now set to the default postgres database. Let's see if any other database exists within this local PostgreSQL server.

1. Enlisting the Available Databases

You can use the \l command to get a list of all available databases.

Enlisting the Available Databases

As you can see, I have the following databases -

  • datacamp_tutorials
  • postgres

You can ignore the rest of the entries. Now, let's see the list of available tables in the current database.

2. Enlisting the Available Tables in the Current Database

\dt does this for you -

Enlisting the Available Tables in the Current Database

But unfortunately, the postgres database does not have any tables created in it (tables are referred to as relations in the database management literature). Let's switch to another database and see if it has any tables created in it.

3. Switching to Another Database

The syntax for doing this: \c <database_name>. Suppose, you want to switch to a database named datacamp_tutorials you can do so like the following -

`\c datacamp_tutorials`

Switching to Another Database

Now, let's see if it has any tables with the \dt command -

As you can see datacamp_tutorials database has the following four tables -

  • countries
  • route_table
  • station_code
  • train_table

Say, you want to view the structural details of a particular table. This is often referred to as describing a table. Let's now see how it can be done.

4. Describing a Particular Table

The general syntax for doing is \d <table_name>. Suppose, you are in the datacamp_tutorials database and you want to describe the table named countries. The command for this would be \d countries -

Describing a Particular Table

As you can see, \d countries gives you lots of valuable information about the table such as its column names, their data-types, modifiers of columns and so on. You may want to know the current version of your PostgreSQL engine. Maybe for documentation purposes. Let's see how can you.

5. Knowing the Version of PostgreSQL

SELECT version(); lets you do this -

Knowing the Version of PostgreSQL

Now, say you have forgotten the last command that you executed on psql shell in between some calls and it was an important one. No problem! You can retrieve that easily.

6. Seeing the Previously Executed Command

\g is there for doing this -

Seeing the Previously Executed Command

As you can see, \g automatically executed the previous command. You might be more curious and want to see the list of all the psql commands available for the PostgreSQL engine that you are running. Let's now see that.

7. Enlisting all the Available Commands

You can get the list of all the available psql commands with \? -

Enlisting all the Available Commands

You may be interested in knowing the syntaxes of different database statements like SELECT, ALTER, DROP and so on. You can do so in the psql prompt itself.

8. Knowing the Syntaxes of PostgreSQL Statements

Let's say you want to know more about DROP statements. \h DROP TABLE can help you with this -

Knowing the Syntaxes of PostgreSQL Statements

Suppose, you want to analyze the execution times of the queries. Let's now see how this can be done from the psql shell.

9. Knowing the Execution Times of Queries

For doing this, you will have to give the \timing command. Let's say you want to analyze the execution time of the following query -

select * from train_table limit 10;

You first give the \timing command -

Knowing the Execution Times of Queries

After the timing option is enabled, you can run your query -

Knowing the Execution Times of Queries

As you can see the time it took to execute the query is 0.786 ms. The execution time may vary from machine to machine. You will now see the final psql command which provides your previously executed command/query in a text editor.

10. psql + text editor

If you execute the \e command, it opens the last executed command/query written in a text editor, and it also lets you edit and rerun it.

psql + text editor

As you can see, the e command opens up the Notepad text editor with the previously executed query.


In this tutorial, you were able to learn about ten handy psql commands which you can use in your day-to-day database-related tasks. If you want to learn more about SQL, you can try out the following DataCamp courses -

SQL Courses

Certification available

Introduction to SQL

BeginnerSkill Level
2 hr
Learn how to create and query relational databases using SQL in just two hours.
See DetailsRight Arrow
Start Course
Certification available

Intermediate SQL

BeginnerSkill Level
4 hr
Accompanied at every step with hands-on practice queries, this course teaches you everything you need to know to analyze data using your own SQL code today!
See MoreRight Arrow

Google Cloud for Data Scientists: Harnessing Cloud Resources for Data Analysis

How can using Google Cloud make data analysis easier? We explore examples of companies that have already experienced all the benefits.
Oleh Maksymovych's photo

Oleh Maksymovych

9 min

SQL vs NoSQL Databases: Key Differences and Practical Insights

Discover how to decide between SQL and NoSQL databases in data science and application development. Learn their strengths, use cases, and industry applications.

Kevin Babitz

15 min

A Guide to Docker Certification: Exploring The Docker Certified Associate (DCA) Exam

Unlock your potential in Docker and data science with our comprehensive guide. Explore Docker certifications, learning paths, and practical tips.
Matt Crabtree's photo

Matt Crabtree

8 min

Bash & zsh Shell Terminal Basics Cheat Sheet

Improve your Bash & zsh Shell skills with the handy shortcuts featured in this convenient cheat sheet!
Richie Cotton's photo

Richie Cotton

6 min

Functional Programming vs Object-Oriented Programming in Data Analysis

Explore two of the most commonly used programming paradigms in data science: object-oriented programming and functional programming.
Amberle McKee's photo

Amberle McKee

15 min

A Comprehensive Introduction to Anomaly Detection

A tutorial on mastering the fundamentals of anomaly detection - the concepts, terminology, and code.
Bex Tuychiev's photo

Bex Tuychiev

14 min

See MoreSee More