How to retrieve the comment of a PostgreSQL database?

Question

I recently discovered you can attach a comment to all sort of objects in PostgreSQL. In particular, I'm interested on playing with the comment of a database. For example, to set the comment of a database:

COMMENT ON DATABASE mydatabase IS 'DB Comment'; 

However, what is the opposite statement, to get the comment of mydatabase?

From the psql command line, I can see the comment along with other information as a result of the \l+ command; which I could use with the aid of awk in order to achieve my goal. But I'd rather use an SQL statement, if possible.

Answer 1

First off, your query for table comments can be simplified using a cast to the appropriate object identifier type:

SELECT description FROM   pg_description WHERE  objoid = 'myschema.mytbl'::regclass; 

The schema part is optional. If you omit it, your current search_path decides visibility of any table named mytbl.

Better yet, there are dedicated functions in PostgreSQL to simplify and canonize these queries. The manual:

obj_description(object_oid, catalog_name) ... get comment for a database object

shobj_description(object_oid, catalog_name) ... get comment for a shared database object

Description for table:

SELECT obj_description('myschema.mytbl'::regclass, 'pg_class'); 

Description for database:

SELECT pg_catalog.shobj_description(d.oid, 'pg_database') AS "Description" FROM   pg_catalog.pg_database d WHERE  datname = 'mydb'; 

How do you find out about that?

Well, reading the excellent manual is enlightening. :)
But there is a more direct route in this case: most psql meta commands are implemented with plain SQL. Start a session with psql -E, to see the magic behind the curtains. The manual:

-E
--echo-hidden

Echo the actual queries generated by \d and other backslash commands. You can use this to study psql's internal operations. This is equivalent to setting the variable ECHO_HIDDEN to on.

Answer 2

To get the comment on the database, use the following query:

select description from pg_shdescription join pg_database on objoid = pg_database.oid where datname = '<database name>' 

This query will get you table comment for the given table name:

select description from pg_description join pg_class on pg_description.objoid = pg_class.oid where relname = '<your table name>' 

If you use the same table name in different schemas, you need to modify it a bit:

select description from pg_description join pg_class on pg_description.objoid = pg_class.oid join pg_namespace on pg_class.relnamespace = pg_namespace.oid where relname = '<table name>' and nspname='<schema name>'