CODEWITHSUNDEEP
postgresql
C2H5OH
C2H5OH

Reputation: 5602

How to retrieve the comment of a PostgreSQL database?

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.

Upvotes: 27

Views: 26292

Answers (6)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656744

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. When omitted, the 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.

Upvotes: 46

mivk
mivk

Reputation: 14834

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

Upvotes: 0

Mukhammadsher
Mukhammadsher

Reputation: 192

This query will return the comment of a table 

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

Upvotes: 0

Geoff Hansen
Geoff Hansen

Reputation: 11

For tables, try

\dd TABLENAME

This shows the comment I added to a table

Upvotes: 1

user3732995
user3732995

Reputation: 41

This query will get only table comment for all tables

SELECT RelName,Description 
FROM pg_Description
JOIN pg_Class 
ON pg_Description.ObjOID = pg_Class.OID
WHERE ObjSubID = 0

Upvotes: 2

martin
martin

Reputation: 2638

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>'

Upvotes: 19

Related Questions