10 Command-line Utilities in PostgreSQL

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:

• Beginner's Guide to PostgreSQL
• Intro to SQL for Data Science by DataCamp

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.

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 -

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`

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 -

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 -

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 -

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 \? -

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 -

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 -

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

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.

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 -

• Joining Data in SQL
• SQL for Exploratory Data Analysis