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.

4 Responses to “List the Databases on a PostgreSQL/PgSQL Server”

  1. August 06, 2009 at 4:17 pm, roger pack said:

    worked like a charm

    Reply

  2. March 13, 2010 at 11:46 am, Anonymous said:

    salamo alikom
    can you provide it in sql query .

    Reply

    • April 29, 2010 at 6:31 pm, Rob said:

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

      Reply

  3. October 14, 2010 at 3:59 pm, Mars99 said:

    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

    Reply

Leave a Reply