List the Databases on a PostgreSQL/PgSQL Server


From within the psql monitor, use the following command to list all of the databases in the server:

\l

Alternatively, you can list the databases from the command line using the command as follows:

psql -l

The Conversation

Follow the reactions below and share your own thoughts.

  • roger pack

    worked like a charm

  • Anonymous

    salamo alikom
    can you provide it in sql query .

    • Rob

      select d.datname as name from pg_catalog.pg_database d;

  • Mars99

    I assume you normally want to see a list of all databases NOT including the default PostgreSQL databases. For that you can execute the following SQL query:
    SELECT datname FROM pg_database WHERE datistemplate IS FALSE AND datallowconn IS TRUE AND datname!=’postgres';

    To use that from the command line (for scripts…e.g. a backup script) you might want to call it using ‘psql':
    psql –quiet –no-align –tuples-only –dbname=postgres –username=postgres –host=127.0.0.1 –port=5432 –command=”SELECT datname FROM pg_database WHERE datistemplate IS FALSE AND datallowconn IS TRUE AND datname!=’postgres';”

    If you don’t want to be asked by psql for the PostgreSQL password (because you run it in batch script), you need to set the following environment variable in your batch script:
    SET PGPASSWORD=yoursecretpassword