/

How to List All Databases Using PostgreSQL

How to List All Databases Using PostgreSQL

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:

  1. Open the psql tool.
  2. Type the command \list or \l and hit Enter.
  3. 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:

1
2
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.

tags: [“PostgreSQL”, “databases”, “SQL query”, “psql”, “templates”]