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:

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.

Conclusion

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 -

Topics

SQL Courses

Certification available

Course

Introduction to SQL

2 hr
566.7K
Learn how to create and query relational databases using SQL in just two hours.
See DetailsRight Arrow
Start Course
See MoreRight Arrow
Related

A Data Science Roadmap for 2024

Do you want to start or grow in the field of data science? This data science roadmap helps you understand and get started in the data science landscape.
Mark Graus's photo

Mark Graus

10 min

Top 5 SQL Server Certifications: A Complete Guide

Unlock SQL Server certification success with our guide on paths, preparation with DataCamp, and the top certifications to enhance your career.
Matt Crabtree's photo

Matt Crabtree

8 min

Mastering SQL ROUND: Precision Handling in Data Analysis

Learn how to use the SQL ROUND function in this tutorial. It covers the implementation of the ROUND function in Oracle, MySQL, PostgreSQL, and SQL Server.
Laiba Siddiqui's photo

Laiba Siddiqui

10 min

Mastering SQL NOT EQUAL Operator: A Beginner's Guide

Unlock the power of SQL NOT EQUAL with our expert guide. Learn to refine data queries with practical examples and optimization tips for better analysis.
Abid Ali Awan's photo

Abid Ali Awan

5 min

SQL NOT IN Operator: A Comprehensive Guide for Beginners

Master SQL's NOT IN operator with this beginner's guide. Learn to filter data effectively, avoid common pitfalls, and explore efficient alternatives
Abid Ali Awan's photo

Abid Ali Awan

5 min

SQL CONTAINS: A Comprehensive Tutorial

Unlock the power of SQL CONTAINS for advanced text searches. Dive into logical operators, proximity searches, and wildcard uses for precise data analysis.
Abid Ali Awan's photo

Abid Ali Awan

5 min

See MoreSee More