When working with PostgreSQL, there are two ways to list all databases: using the psql
tool or by executing an SQL query.
Listing databases using psql
To list all databases using psql
, follow these steps:
- Open the
psql
tool. - Type the command
\list
or\l
and hit Enter. - PostgreSQL will display a list of databases and templates.
Here is an example of the database list:
airbnbclone
nextbnb
postgres
test
In addition to databases, PostgreSQL also includes templates. Templates are pre-populated databases that serve as a base for creating new databases using the syntax CREATE DATABASE databasename TEMPLATE template0
.
By default, when you create a new database using the CREATE DATABASE databasename
command, PostgreSQL uses template1
as the template. However, this topic is not crucial at the moment, but it’s good to understand what templates are.
If you want a more detailed view that includes the disk size of each individual database, you can use the command \list+
or \l+
in psql
.
Listing databases using an SQL query
To list databases using an SQL query, you can execute the following query:
SELECT datname FROM pg_database
WHERE datistemplate = false;
This query will display a list of databases, excluding templates.
You can see an example of the database list in the following screenshot:
{insert screenshot here}
By following these methods, you can easily list all databases in PostgreSQL.