1

In PostgreSQL is it possible to generate a list of all databases and all schema with a single psql command?

To be concrete, let me provide an example.

I have two databases in a PotgreSQL database cluster: accounts and people. In the accounts database there are these schema (beyond the default): hr and finance. In the people database there are these schema (beyond the default): hr, admin, backup.

Is there a single psql command I can run that generates results something like this:

database | schema
-----------------
accounts |  hr
accounts |  finance
people   |  hr
people   |  admin
people   |  backup

Or is the only way to generate this information to connect multiple times and run separate queries?

1

In PostgreSQL is it possible to generate a list of all databases and all schema with a single psql command?

No. The list of schemas of a database is private to that database and cannot be obtained without being connected to it. The list of databases is available with \l or select * from pg_database.

0

Daniel Vérité noted that

The list of schemas of a database is private to that database...

One can see this by looking at the documentation for System Catalogs, Overview page which says, in part,

Most system catalogs are copied from the template database during database creation and are thereafter database-specific. A few catalogs are physically shared across all databases in a cluster; these are noted in the descriptions of the individual catalogs.

Looking at the pg_namespace documentation it is clear that pg_namespace is not shared. (For an example that is shared, see pg_database).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.