We have used the built-in commands and functions earlier to create or delete tables like “CREATE TABLE”, “DELETE TABLES” for listing tables or databases; there are no specific built-in commands available in PostgreSQL. PostgreSQL doesn’t support commands like “list database” or “Show Database” for listing the databases in a server. To list all the databases currently running in a server, users can adopt different ways in PostgreSQL. PostgreSQL has its own command-line tool psql, which can be used to list databases using meta-commands and SQL queries, or you can simply use pgAdmin4 for listing all databases present on a server. In this article, we will be talking about efficient ways for the listing database in PostgreSQL.
Different Ways to List Database in PostgreSQL:
You need to find and list the database on your server and have no idea how to do that; then, this guide is the right place for you to find the answers to your questions. This will not only provide you one method but three with explained examples to list databases residing on your server by:
- Using pgAdmin4.
- Using psql with meta-commands.
- Using psql with SELECT statement.
1. List Database Using pgAdmin4 in PostgreSQL:
When you installed the PostgreSQL setup, you were provided with a server, for which you have set up a password. When you enter a server, you can see a database; there, you can create your own databases by right-clicking on it, as you can see below:
Once you click on the database, this screen will pop up:
In the “Database” text box, write your preferred database name and save the changes. The new database will be created that you can view later by using the “Select” statement in the Query tool.
You can also confirm how many databases are present on your server. On the left side navigation menu of pgAdmin4, you can see the “Database” label; by clicking on that, a drop-by list will appear: all the databases on your server. For further information, you can click on the properties tab.
Above, you can see all the listed databases on your server, including the one we created in the above example.
What are Meta-commands?
Psql supports the meta-commands, which are also called backslash or slash (\). You can run SQL queries to the PostgreSQL command-line tool too, but meta-commands make psql convenient for scripting purposes which they process themselves. Some of the examples of meta-commands are mentioned further in this article in psql.
2. List Databases Using psql with Meta-commands in PostgreSQL:
We will now be moving on, how to list the database on your server. Once you have opened the psql tool, select the by default database and don’t enter any specific database, this will not list databases present on your server because you will be in the database itself; the below image shows this:
You are now entered in the default database “postgres” created by PostgreSQL itself. Now, execute the following meta-command to list the databases existing on a server:
The command “\l” will return and list the databases on your server and display them as:
The above table displays databases’ information, including their name, owner, encoding, collate, ctype, and access privileges.
If you want more information on databases, then run the below command:
The above command “\l+” will return the extended information of databases, including the size, tablespace, and description. The “\l+” command displays the following results:
Note, you can also use the “\list” command in place of “\l” and “\list+” command in place of “\l+”.
3. List Database Using psql with SELECT Statement:
We have seen how to list databases using meta-commands, now we will be learning how to list databases with “SELECT” statement or SQL query using psql tool. We will be using “pg_database” in our “SELECT” statement because “pg_database” stores all the information of databases in the current server. Execute the following query to list databases using the “SELECT” statement:
The “SELECT” statement will select the column “datname” from the “pg_database” and display only that column in the results as displayed below:
All the databases are now listed in the above image that is present in a server. You can also confirm the databases by looking up the above examples we have performed.
If you want all the information of the databases, then run the following query:
This query will display the results of databases with all information in them. You can select any specific column to be displayed in the results as per your requirements. I have selected two columns in the below example to display the “datname” and “oid” of a database:
By selecting two columns, “oid” and “datname” the query will display only these columns in the results.
Note, if you want to mention a condition in your query, then use the “WHERE” clause in the statement to display the records of databases you require. Below, I have demonstrated one example with the “WHERE” condition:
In the “WHERE” condition, I have displayed only the records where the “datistemplate” column value is equal to false and terminated other values than that, which shows the following result:
Conclusion:
This article was based on the process of how to list the databases residing in a server. In this article, we have acquired knowledge on listing databases using different ways. We have used pgAdmin4, psql meta-commands, and psql “SELECT” statements to list the database in PostgreSQL. All methods proved successful to list databases in PostgreSQL; each of the methods depends on how you want to display the information of your databases. The first method displays lesser information, but the second and third methods display detailed information of a database. It’s up to users which method they prefer the most among the three for the listing databases.
