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 isactive
.You must also make sure that
psql
, the command-line PostgreSQL interface, is installed. The commandpsql -V
can be used to verify thatpsql
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:
1 | 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:
1 | 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:
1 | CREATE DATABASE db_name; |
For our example, we’ll create the test
database:
1 | 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:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | 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) |
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:
1 | SELECT datname FROM pg_database; |
The SELECT statement shown above should return results that look like this:
1 2 3 4 5 6 7 8 9 10 11 12 | 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