How to query PostgreSQL for a list of schemata with comments?

Question

I'm trying to get a list of schemata in PostgreSQL (9.6) along with any comments which might have been added...

This works great to get a list of schema names:

select schema_name
from information_schema.schemata

...but how do you get the associated comment (if there is one) as well?

• I've found loads of stuff to get database/table/column comments... schema comments seem to be evading me?

Any help gratefully received!

Answer 1

Use pg_namespace

select nspname as schemaname, 
 obj_description(oid, 'pg_namespace') as comment
from pg_namespace;

---

A good way to learn statements like that is to run psql with the --echo-hidden option and then use one of the meta commands to display the information.

If you e.g. run \dn+ in psql you see:

postgres=> \dn+
********* QUERY **********
SELECT n.nspname AS "Name",
 pg_catalog.pg_get_userbyid(n.nspowner) AS "Owner",
 pg_catalog.array_to_string(n.nspacl, E'\n') AS "Access privileges",
 pg_catalog.obj_description(n.oid, 'pg_namespace') AS "Description"
FROM pg_catalog.pg_namespace n
WHERE n.nspname !~ '^pg_' AND n.nspname <> 'information_schema'
ORDER BY 1;
**************************
 List of schemas
 Name | Owner | Access privileges | Description
-----------+----------+----------------------+------------------------
 public | postgres | postgres=UC/postgres+| standard public schema
 | | =UC/postgres |
 stuff | postgres | |
 tablefunc | postgres | postgres=UC/postgres+|

• Tim Needham: This is perfect... and thanks for the great tip @a_horse_with_no_name !