PostgreSQL list databases

In this PostgreSQL tutorial, we are going to learn about PostgreSQL List Databases. Here we will learn how to list databases available in PostgreSQL, and we will also cover the following list of topics.

• Postgresql list databases
• Postgresql list databases sql
• Postgresql list databases from command line
• Postgresql list database size
• Postgresql list databases psql
• Postgresql list databases names
• Postgresql list databases bash
• Postgresql list databases privileges
• Psql list databases with size

PostgreSQL List Databases:

Now, we will learn the way to list databases in the PostgreSQL database server. In MySQL, we used to show all databases within the database server using the SHOW DATABASES statement.

But, the SHOW DATABASES statement is not directly supported by PostgreSQL. It provides something similar with two methods to show databases in the present database server.

Listing databases in PostgreSQL using psql command

Multiple databases are managed by a single Postgre server process at a similar time. Every database is stored as a separate set of files in its directory in the server’s data directory.

If we are using the psql tool to connect to the PostgreSQL database server, we will use the \list command or its shortcut \l command to list all the databases in the server.

Read: PostgreSQL Data Types + Examples

PostgreSQL list databases size

In Postgresql, we can be managing several databases on a cluster.
We may need to query the size of all of these databases in some cases.

We can query the size of one database or all databases in the cluster with the help of the below script:

SELECT pg_database.datname as "databasename", 
pg_database_size(pg_database.datname)/1024/1024/1024 AS sizegb 
FROM pg_database ORDER by pg_database_size(pg_database.datname) DESC;

The output of the above query is shown in the image below.

Read Create a stored procedure in PostgreSQL

Find the Size of a single Database in PostgreSQL

In this section, we will learn how to find the size of a single database in PostgreSQL. The query for this implementation is given below.

SELECT pg_database.datname as "databasename", 
pg_database_size(pg_database.datname)/1024/1024/1024 AS sizegb 
FROM pg_database
WHERE pg_database.datname='Test'
ORDER by pg_database_size(pg_database.datname) DESC;

The output for the above query is given below.

Read: Postgresql date add

Postgresql list databases privileges

PostgreSQL has some convenient functions that are used to notice regarding the privilege of a user on a particular Database object. Perhaps we will listing of users and their privileges for a database by using the \du command. Let’s check how it works with \du command.

With the use of \du command, we can get an output like the one below. Here, “Postgres” is the only predefined role and has the superuser attribute. We need to connect with this role in the first step to create more roles.

Now we will check with \dp command:

With the help of the \dp command, we can check the privileges on the existing tables of roles. If we want a method to list the privileges of a particular database, but we already figured it out.

The owner of the database has always all privileges and we can add more privileges on the database to other users/groups. Those can be listed using the \l command.

PostgreSql list databases with command line

We can list databases with the help of two commands which are \l and \l+. Let’s see how it works. When we use \l command the output is like:

And if we use the \l+ command the following output is shown:

A list of the names, owners, character set encodings, and acquire privileges of all the databases in the server. If + is appended to the command name, database sizes, default tablespaces, and descriptions are also shown. The size information is individually accessible for databases that the current user can connect to.

Apart from using \l and \l+ commands, there is another method which is the SELECT statement. Let’s see the query for the SELECT statement.

SELECT datname FROM pg_database;

Read: Postgresql date between two dates

PostgreSql list database bash

Bash is a command processor that generally runs in an exceedingly text window wherever the user types command that cause actions. Bash may also read and execute commands from a file, known as a shell script.

Let’s connect the database using the command and will check the output for the same. The command to list databases in bash is given below.

psql -c '\l'

You may also like to read the following articles.

• How to create a view in PostgreSQL
• PostgreSQL list users
• Postgresql function return table
• Postgresql difference between two timestamps
• Postgresql create user with password
• Postgresql create database
• Postgresql ilike case insensitive
• Postgresql group_concat
• Drop Database PSQL

In this tutorial, we have learned about PostgreSQL List Databases. Here we have learned how to list databases available in PostgreSQL, and we have also covered the following list of topics.

• Postgresql list databases
• Postgresql list databases sql
• Postgresql list databases from command line
• Postgresql list database size
• Postgresql list databases psql
• Postgresql list databases names
• Postgresql list databases bash
• Postgresql list databases privileges
• Psql list databases with size