Introduction to PostgreSQL Show Databases
The PostgreSQL provide show database command. The show database command is used to see how many databases are present on the server and this command works on command line tool psql as well as query tool in pgadmin4, this is the most common task of administrator. The PostgreSQL in which single progres(by default database) is able to store multiple databases and each database stored set of files. The PostgreSQL provides a different kind of feature to user like create a database, drop database and show database etc.
Psql provide the facility to connect the server and execute a different query against the server. MySQL uses show databases command to show database but 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 are 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:
- In the above syntax where datname is used to show available databases from pg_database are by default catalog it is used to store all databases.
How does Show Databases Statement work in PostgreSQL?
Before going to see how to show database statement is work, we need some prerequisites as follows:
- First, you must install PostgreSQL on your system.
- Use below command to check PostgreSQL is working properly 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 it can be accessed when the 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 to the listing of the database for PostgreSQL database.
Output:
In 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 database by using following statement.
Code:
create database test;
Output:
Then use \list Meta Command to see the database.
Code:
\list
Output:
We can also show the database by using PostgreSQL query:
Example #1
First, we create a database with name demo.
Code:
create database demo;
Output:
After we perform show database query as follow:
Code:
select datname from pg_database;
Output:
In the above snapshot, the created database demo shows in the fifth number.
Another example of a database is before creations of a new database we need to see how many databases may be available on the server that time we use the following query.
Code:
select datname from pg_database;
Output:
The above snapshot shows 3 by default databases like Postgres, template1, template0 and college is a user-created database.
Example #2
PostgreSQl provide the facility to create database with 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 a number of databases and we need to only see those names start d characters. PostgreSQL provides such a facility.
Code:
Select datname from pg_database
where datname like 'd%';
Explanation:
- In the above statement, we find out all databases whose name start with d using the like operator.
Output:
Let’s see another situation: suppose we need a database that name ends with e.
Code:
select datname from pg_database
where datname like '%e';
In the above statement where datname is database name, pg_database by default database of PostgreSQL here we use like operator to show database.
Output:
Example #4
Suppose in our server number of database 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 result as shown in the snapshot.
After Execution of the above statement result as shown in the snapshot.
In the above snapshot, it discards the database name whose start with t character.
Conclusion
From the above article, we saw show database Meta command as well as we implement show database by using query statements. In a query statement, we use like operator to perform a smooth search on the database that means as per our requirement we perform the show database. Finally, it is a time-saving process in PostgreSQL.
Recommended Articles
This is a guide to PostgreSQL Show Databases. Here we discuss the introduction and how does show databases statement work in PostgreSQL? You may also have a look at the following articles to learn more –
