How to List PostgreSQL Databases and Tables Data Using PSQL Command

So far, we have explored PostgreSQL, learned how to access its psql shell, created a basic database, and verified user credentials. In this lesson, we will delve into some of the psql commands that we can execute before moving on to SQL.

The psql shell provides a variety of commands that enable us to perform various operations on the database, such as creating tables, inserting data, querying data, and modifying table schema. By using psql commands, we can interact with the database and get useful insights into its structure and contents.

In the upcoming lessons, we will learn how to write SQL queries to retrieve data from the database and perform various operations on it. However, before that, we need to understand how to use psql commands to navigate and manage the database.

So, let’s explore some of the essential psql commands that we can use inside the psql shell.

Principles of Writing Code in SQL

Principles of writing high-quality SQL code:

  • Use Consistent Casing – Always use a consistent casing for SQL commands, keywords, and identifiers. SQL commands should be written in uppercase letters, while all other identifiers like table names, column names, and variables should be in lowercase.
  • Use Meaningful and Descriptive Names – Use meaningful and descriptive names for tables, columns, and variables that reflect their purpose and content. This makes the code easier to read and understand, especially for others who may work with the code in the future.
  • Avoid Using Reserved Keywords – Avoid using SQL reserved keywords as table or column names. If you have to use a reserved keyword, wrap it in quotes or brackets to make it clear that it’s an identifier and not a command.
  • Use Whitespace and Indentation – Use whitespace and indentation to make your code more readable and organized. Indent your SQL statements to indicate their relationship and use line breaks to separate different clauses of a statement.
  • Comment Your Code – Add comments to your SQL code to explain the purpose and functionality of each statement or block of code. This makes it easier for others to understand and maintain the code in the future.
  • Use Parameterized Queries – Always use parameterized queries to avoid SQL injection attacks and to ensure data integrity. Parameterized queries separate the SQL code from the data values, making it harder for malicious users to manipulate the SQL code.
  • Test Your Code – Always test your SQL code thoroughly before deploying it to production. Check for syntax errors, data inconsistencies, and performance issues. Use test data that covers all possible scenarios and edge cases.

By following these principles, you can write SQL code that is easy to read, understand, and maintain, and that ensures data integrity and security.

PSQL Commands Usage

PSQL is an interactive interface program for communicating with PostgreSQL, and it has several flags to control the connection.

Some of the essential flags are:

  • -h – Specifies the host to be contacted either by IP address or by name if the DNS server can recognize it.
  • -U – Specifies the username through which we want to connect.
  • -p – Specifies the port we want to connect through (the default is 5432).

For example, to connect to a PostgreSQL server running on the localhost with the username “username“, we can use the following command:

$ psql -h localhost -U username

Alternatively, we can use an entire text string as a single medium containing the entry parameters to the database, as shown below:

$ psql "dbname=dbname host= user=postgres password=password port=5432 sslmode=require"

For instance, we can execute SQL queries, create tables, alter table schema, and manage users and permissions using the psql commands. To get help on the available commands, we can type “help” at the prompt.

Using Exiting PostgreSQL Database

In this series, we will be designing multiple tables for our database. However, to expedite the process and avoid creating some tables and including the data contained within, we will use an existing database that is available for download.

Follow these steps in the Psql shell (not within the Postgres shell):

First, Connect to the Psql shell with:

$ sudo -i -u postgres

Then download the data by running the following curl command:

$ curl -L -O

Next, create a new database in Postgres with the following command:

createdb geeksveda

Upload the downloaded data to the database using the following command:

$ pg_restore --no-owner --dbname geeksveda download
Restore PostgreSQL Database
Restore PostgreSQL Database

Here’s a breakdown of the different components of the command:

  • pg_restore is the command used to restore a PostgreSQL database.
  • --no-owner is an option that tells pg_restore to exclude any ownership-related commands from the backup file. This can be useful if the backup file was created on a different server and you don’t want to import ownership information.
  • --dbname geeksveda specifies the name of the database that you want to restore the backup file into.
  • download is the name of the backup file that you want to restore.

Note: We will discuss the pg_restore command and the createdb command in later lessons.

To connect to the geeksveda database, you can use one of the following commands:

$ psql --dbname geeksveda
\c geeksveda
Connect PostgreSQL Database
Connect PostgreSQL Database

Get a List of PostgreSQL Databases

To view a list of all stored databases, you can use the command \l in PostgreSQL, which will display the database name, owner, encoding, collation, character type, and access privileges.

To obtain additional information about each database, you can use the \l+ command, which will show a detailed list of all the databases, including the size and the number of tables and indexes in each database.

Here is an example of the \l command output:

List of PostgreSQL Databases
List of PostgreSQL Databases

If you want to get more detailed information about the databases, use the "\l+" command:

PostgreSQL Database Info
PostgreSQL Database Info

This output provides additional information about each database, including its size, the number of tables, and the type and owner of each database.

Retrieving PostgreSQL Database Tables

To retrieve a list of tables in a database, you can use the command \dt, which will display a table with the schema, name, type, and owner of each table in the database. For example:

View PostgreSQL Database Tables
View PostgreSQL Database Tables

To describe a specific table in more detail, use the command \d followed by the name of the table, which will display the column names, data types, and any constraints or indexes on the table. For example:

View PostgreSQL Table Info
View PostgreSQL Table Info

To display more information about the tables or users, add a "+" symbol after the command. For example, \dt+ will display additional information about each table, while \dg+ will display more details about each user role.

View PostgreSQL Table Roles
View PostgreSQL Table Roles

Get PostgreSQL Help

To display the documentation for the SELECT statement in SQL, we can use the following command:

View Select Statement Help
View Select Statement Help

This command will provide information on the syntax, usage, and options available for the SELECT statement in SQL. It can be helpful for beginners or those who need a quick reference while working with SQL queries.

Exit Postgres Shell

To exit the Postgres shell (PSQL), use the command \q, which will allow you to quit the shell and return to your command prompt or terminal.


In this post, we have covered a wide range of topics related to PostgreSQL and the psql shell. We have discussed how to create a basic database, verify user credentials, and use psql commands to perform various operations on the database.

Additionally, we have explained the principles of writing high-quality SQL code, which include using consistent casing, meaningful and descriptive names, whitespace and indentation, comments, parameterized queries, and testing.

Furthermore, we have provided guidance on how to connect to an existing database, retrieve a list of databases, and how use essential flags to control the connection in PostgreSQL. By following these tips and best practices, you can become proficient in managing and querying databases using PostgreSQL and the psql shell.

Overall, this post aims to provide a comprehensive guide to working with PostgreSQL and the psql shell, and we hope it has been helpful in expanding your knowledge and skills in this area.

If you read this far, tweet to the author to show them you care. Tweet a thanks
Mead Naji is a freelance developer with extensive experience in web development and Linux system administration. Since January 2022, Mead has been working on developing an e-commerce solution with a new payment system like Stripe, as well as doing one-on-one teaching sessions.

Each tutorial at GeeksVeda is created by a team of experienced writers so that it meets our high-quality standards.

Join the GeeksVeda Weekly Newsletter (More Than 5,467 Programmers Have Subscribed)
Was this article helpful? Please add a comment to show your appreciation and support.

Got Something to Say? Join the Discussion...