PostgreSQL Show Databases

Updated May 24, 2023

Introduction to PostgreSQL Show Databases

The PostgreSQL provides a show database command. The PostgreSQL Show Databases command is used to see how many databases are present on the server, and this command works on the command line tool psql as well as the query tool in pgadmin4, this is the most common task of the administrator. PostgreSQL, in which single progres(by default database) can store multiple databases, and each database stores a set of files. The PostgreSQL provides different kinds of features to users, like creating a database, drop database and show database, etc.

Psql provides the facility to connect the server and execute a different query against the server. MySQL uses the show databases command to show database, but the show database statement does not work directly in PostgreSQL, it provides some different meta-commands to show database and different query statement.

Syntax of PostgreSQL Show Databases

Given below is the syntax:

\l or \list

Explanation:

• In the above syntax, where \l is used to list all database lists from the server and the same database list, we can get by using another syntax \list.
• The function of both syntaxes is the same. It is a Meta command we directly perform on the terminal.

select datname from pg_database;

Explanation:

• The catalog stores all databases in the above syntax, where datname is used to show available databases from pg_database by default.

How does Show Databases Statement work in PostgreSQL?

Before going to see how to show database statement works, we need some prerequisites as follows:

• First, you must install PostgreSQL on your system.
• Use the below command to check whether PostgreSQL is working correctly or not. If the status is active, that means PostgreSQL is installed successfully.

Code:

service postgresql status

• Also, install the psql command-line PostgreSQL interface.
• Multiple databases are available to users but can be accessed when a connection is established between client and server.
• Users perform different operations on databases with the help of show database commands by using like operators.

Show Database Statement as follows:

Code:

\l

This is Meta Command. It is used for the listing of the database for the PostgreSQL database.

Output:

The above screen snapshot, it lists all databases that are present on the server.

Code:

\list

This is also Meta Command, the function of \List is the same as \l.

Output:

We create a database by using the following statement.

Code:

create  database  test;

Output:

Then use \list Meta Command to see the database.

Code:

\list

Output:

We can also show database by using the PostgreSQL query:

Example #1

First, we create a database with a name demo.

Code:

create  database   demo;

Output:

After we perform the show database query as follows:

Code:

select    datname     from    pg_database;

Output:

The created database demo shows the fifth number in the above snapshot.

Another example of a database is before creating a new database, we need to see how many databases may be available on the server then, so we use the following query.

Code:

select    datname    from    pg_database;

Output:

The above snapshot shows 3 by default databases like, Postgres, template1, template, and college is, a user-created database.

Example #2

PostgreSQl provides the facility to create a database with the owner.

Code:

create  database  demo1  with  owner =pg_monitor;

In the above statement, we use a create a statement to create a database, and we created a demo1 database with owner pg_monitor.

Output:

Now see the database column demo1 is created.

Code:

select   datname  from   pg_database;

Output:

Example #3

In PostgreSQL, by default database is Postgres, but different users created several databases, and we need only to see those names start with d characters. PostgreSQL provides such a facility.

Code:

Select   datname      from    pg_database
where      datname     like 'd%';

Explanation:

• In the above statement, we find all databases whose names start with d using the like operator.

Output:

Let’s see another situation: suppose we need a database whose name ends with e.

Code:

select      datname       from   pg_database
where      datname     like '%e';

In the above statement where datname is the database name, pg_database by default database of PostgreSQL, here we use the like operator to show the database.

Output:

Example #4

Suppose in our server number of databases is present, and we don’t want to see those database names start with t.

Code:

select   datname     from      pg_database
where   datname   not like 't%';

Explanation:

• In the above statement, we use not like operators to get the desired result.

Output:

Before the Execution of the above statement, the result is shown in the snapshot.

After the Execution of the above statement, the result is shown in the snapshot.

The above snapshot discards the database name that starts with a t character.

Conclusion

From the above article, we also saw the show database Meta command as we implemented the show database using query statements. In a query statement, we use a like operator to perform a smooth search on the database means that as per our requirement, we perform the show database. Finally, it is a time-saving process in PostgreSQL.

Recommended Articles

We hope that this EDUCBA information on “PostgreSQL Show Databases” was beneficial to you. You can view EDUCBA’s recommended articles for more information.

1. PostgreSQL MAX
2. PostgreSQL RAISE EXCEPTION
3. Logical Replication in PostgreSQL
4. PostgreSQL Formatter