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
You must also make sure that
psql, the command-line PostgreSQL interface, is installed. The command
psql -Vcan be used to verify that
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
CREATE DATABASE test;
List databases for Postgres in psql
\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 |
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
SELECT datname FROM pg_database;
The SELECT statement shown above should return results that look like this:
postgres=# SELECT datname FROM pg_database;
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:
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