Calculate size in Postgresql Database?

List all the databases and their sizes in PostgreSQL

The  query will get you a list of all the databases.

select * from pg_database;    

Only list of databases.

select datname  from pg_database;  

If you will execute above query you will see that Postgres servers have three databases defined by default named template0, template1 and postgres. template0 and template1 are skeleton databases that are or can be used by the CREATE DATABASE command. 

postgres is the default database you will connect to before you have created any other databases.

How to get size of each database in PostgreSQL

Sometime we need to show database size in our application, to achieve this we will execute below query to get database size in MBs.

select pg_size_pretty(pg_database_size(pg_database.datname)) from pg_database;  

Below is the complete query to get database name and their sizes in MBs.

 SELECT pg_database.datname, pg_database_size(pg_database.datname), pg_size_pretty(pg_database_size(pg_database.datname)) 

FROM pg_database 

ORDER BY pg_database_size DESC;