How to retrieve the comment of a PostgreSQL database?

user_1100958 0
Viewed 20149

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.

postgresql
6 Answers

This query will return the comment of a table 

 SELECT obj_description('public.myTable'::regclass)
 FROM pg_class
 WHERE relkind = 'r' limit 1
user_75275040

To get the comments on all the databases (not on their objects like tables etc.) :

SELECT datname, shobj_description( oid, 'pg_database' ) AS comment
FROM pg_database
ORDER BY datname

An example showing databases, sizes and descriptions from a shell script:

psql -U postgres -c "SELECT datname,
 format('%8s MB.', pg_database_size(datname)/1000000) AS size,
 shobj_description( oid, 'pg_database' ) as comment
 FROM pg_database ORDER BY datname"

Sample output:

       datname        |     size     |                       comment                       
----------------------+--------------+-----------------------------------------------------
 last_wikidb          |       18 MB. | Wiki backup from yesterday
 postgres             |        7 MB. | default administrative connection database
 previous_wikidb      |       18 MB. | Wiki backup from the day before yesterday
 some_db              |       82 MB. | 
 template0            |        7 MB. | unmodifiable empty database
 template1            |        7 MB. | default template for new databases
user_1110360
Related