25

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.

C2H5OH
  • 5,452
  • 2
  • 27
  • 39

6 Answers6

43

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.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • 2
    I didn't know about `psql -E`, probably due to reading the psql man page too quickly. Thanks. – C2H5OH Jul 16 '12 at 10:01
18

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>'
martin
  • 2,520
  • 22
  • 29
  • 1
    Weird that the comment isn't available in `information_schema`. It should be, since `pg_catalog` structure isn't guaranteed to be stable from major release to major release. – Craig Ringer Jul 16 '12 at 01:37
  • 2
    @CraigRinger: The dedicated functions I describe in my answer will keep working in any case. – Erwin Brandstetter Jul 16 '12 at 18:43
  • @ErwinBrandstetter Great, that's a much better option - much safer. – Craig Ringer Jul 16 '12 at 23:53
  • 3
    **for column comments** one has to utilize the `pg_description.objsubid`: `select c.table_name, c.column_name, c.ordinal_position, d.objsubid, d.description from pg_description d join information_schema.columns c on ( c.table_schema = current_schema and current_schema||'.'||c.table_name)::regclass = d.objoid and c.ordinal_position = d.objsubid )` – Andreas Covidiot Apr 16 '15 at 06:51
  • 1
    One can use `current_database()` in place of `''` for the currently connected database. – Pocketsand Sep 01 '17 at 14:47
2

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
Soner Gönül
  • 97,193
  • 102
  • 206
  • 364
user3732995
  • 31
  • 1
  • How to select ALL tables regardless they have comments or not; meaning both with and without comments in given schema? – jdow Jul 09 '21 at 18:44
1

For tables, try

\dd TABLENAME

This shows the comment I added to a table

Cobra_Fast
  • 15,671
  • 8
  • 57
  • 102
Geoff Hansen
  • 11
  • 1
  • For functions, inside a `psql` session, one can do `\df+ func_name` to get the description too. – Rafs Aug 15 '23 at 13:12
0

This query will return the comment of a table 

 SELECT obj_description('public.myTable'::regclass)
 FROM pg_class
 WHERE relkind = 'r' limit 1
Mukhammadsher
  • 182
  • 3
  • 21
0

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
mivk
  • 13,452
  • 5
  • 76
  • 69