How to Show Databases in PostgreSQL

Show databases in PostgreSQL introduction

If you’re working with PostgreSQL, it’s likely that you have more than one database on your PostgreSQL server. There may be times when you need to get a list of all databases that are on the system. Fortunately, there are a couple of simple ways to accomplish this task. In this article, we’ll explain how to show databases in PostgreSQL using two different methods.

Prerequisites to using PostgreSQL

In this tutorial, we’ll be executing various database commands, so there are a few prerequisites that need to be in place before moving forward:

• First, you must ensure that PostgreSQL is installed on your machine. If you’re not sure, use the command service postgresql status, which will let you know if the status is active.

• You must also make sure that psql, the command-line PostgreSQL interface, is installed. The command psql -V can be used to verify that psql is installed.

Accessing a PostgreSQL database with ‘psql’

We can use the following command to access a PostgreSQL database on a local machine using the psql command-line interface:

This command will prompt you for the password, so you’ll need to enter it in order to get access to PostgreSQL.

Next, type the following command to access your database:

Again, you’ll be prompted for the user’s password, so you’ll need to enter it again and press RETURN.

Create a PostgreSQL Database

The next thing we’ll do is create a PostgreSQL database. To follow along, you’ll need to open the command prompt in Windows or the terminal on MacOS or Linux. The following syntax can be used to create a new database:

For our example, we’ll create the test database:

List databases for Postgres in psql

The \l command in psql can be used to show all of the PostgreSQL databases. The \list command can also be used to achieve the same results.

When you use this command, the returned output should look like this:

Show databases using the ‘SELECT’ statement

While it’s easy and convenient to use the psql commands shown in the previous section, there’s also another way to show databases in PostgreSQL. We can use the following SELECT query in the psql interface:

The SELECT statement shown above should return results that look like this:

We can see from the results that the pg_database table collects information about the available databases in PostgreSQL.

If you’d like to connect to a specific database, you can use the command \c followed by the database name. This will allow you to enter the database and start performing queries. For example: \c some_database.

Conclusion on how to show databases in PostgreSQL

If you have PostgreSQL installed on your machine, it’s important to know that a single instance of PostgreSQL server can manage a number of different databases. There are some simple commands available that can be used to obtain a list of the databases on your PostgreSQL server. In this article, we looked at a few different ways to show databases in PostgreSQL. Using the examples and instructions provided in this tutorial, you’ll be able to retrieve your own list of databases and better manage your PostgreSQL server.