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.

Screenshot of a terminal window getting the PostgreSQL server status and psql version

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:

sudo su - postgres

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:

psql some_username -h 127.0.0.1 -d some_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:

CREATE DATABASE db_name;

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

CREATE DATABASE test;

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:

List OF DATABASES
Name | Owner | Encoding | COLLATE | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
mydb | postgres | UTF8 | en_PH.UTF-8 | en_PH.UTF-8 |
postgres | postgres | UTF8 | en_PH.UTF-8 | en_PH.UTF-8 |
sample | postgres | UTF8 | en_PH.UTF-8 | en_PH.UTF-8 |
sampledb | postgres | UTF8 | en_PH.UTF-8 | en_PH.UTF-8 |
template0 | postgres | UTF8 | en_PH.UTF-8 | en_PH.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_PH.UTF-8 | en_PH.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
test | postgres | UTF8 | en_PH.UTF-8 | en_PH.UTF-8 |
testdb | postgres | UTF8 | en_PH.UTF-8 | en_PH.UTF-8 |
(8 ROWS)

Screenshot of psql listing all of the databases for PostgreSQL

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:

SELECT datname FROM pg_database;

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

postgres=# SELECT datname FROM pg_database;
datname
-----------
postgres
testdb
template1
template0
sample
sampledb
mydb
test
(8 ROWS)

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.

Pilot the ObjectRocket Platform Free!

Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.

Get Started

Keep in the know!

Subscribe to our emails and we’ll let you know what’s going on at ObjectRocket. We hate spam and make it easy to unsubscribe.