List PostgreSQL databases

Inside psql, list the databases with:

\l

                                List of databases
   Name    | Owner  | Encoding | Locale | ... | Access privileges
-----------+--------+----------+--------+-----+-------------------
 notes_app | flavio | UTF8     | C      |     |
 postgres  | flavio | UTF8     | C      |     |
 template0 | flavio | UTF8     | C      |     | =c/flavio
 template1 | flavio | UTF8     | C      |     | =c/flavio

A brand-new cluster already contains three databases, and none of them is yours.

postgres is the normal maintenance database. It exists so administrators and tools always have somewhere to connect, even before any application database exists. That is why the earlier lessons started with psql postgres.

The other two are templates. PostgreSQL never creates a database from nothing: CREATE DATABASE clones an existing one. By default it copies template1, so anything you add to template1 shows up in every database you create afterwards. template0 stays clean for restores and for databases that need different locale settings.

That default has a sharp edge. Connect to template1 by accident, create a table there, and every future database silently inherits it. If \l shows objects you never asked for in new databases, inspect template1.

Sizes and the SQL alternative

Use \l+ when you also need sizes and tablespaces:

\l+

The extra columns show how much disk each database uses, which is the quickest way to spot the database that grew unexpectedly.

\l is a psql meta-command, so it only exists inside psql. From application code or another client, query the catalog directly:

SELECT datname FROM pg_database
WHERE datistemplate = false;

  datname
-----------
 postgres
 notes_app

The datistemplate = false filter hides template0 and template1, matching what you usually mean by “my databases”. Drop the filter to see everything, templates included.

Listing databases is also your verification step after CREATE DATABASE or a restore: if the new name does not appear in \l, the command did not do what you thought, and the next thing to check is which server and cluster you are connected to with \conninfo.