Getting list of table comments in PostgreSQL

Postgresql allows adding comments to objects such as tables. For example I've added a comment to table "mytable" by using this SQL command:

COMMENT ON TABLE mytable IS 'This is my table.';

My question is: If I want to use a SQL-command to get all tables along with their respective comment - how would I do this? What would be the appropriate query for this?

Thanks in advance! Cheers!

Answers


All comments are stored in pg_description

To get the comments on a table, you need to join it to pg_class

As an alternative you can also use the function obj_description() to retrieve this information:

SELECT obj_description(oid)
FROM pg_class
WHERE relkind = 'r'

Edit

In psql you can simply use the \d+ command to show all tables including their comments. Or use the \dd command to show all comments in the system


I am using an useful alias (fa├žade) for obj_description, that is easy to use, because the table name (relname) is a varchar and can be expressed with a separated field for schema name, as in the main tables and queries.

 CREATE FUNCTION rel_description(
     p_relname varchar, p_schemaname varchar DEFAULT NULL
 ) RETURNS text AS $f$
    SELECT obj_description((CASE 
       WHEN strpos($1, '.')>0 THEN $1
       WHEN $2 IS NULL THEN 'public.'||$1
       ELSE $2||'.'||$1
            END)::regclass, 'pg_class');
 $f$ LANGUAGE SQL;
 -- EXAMPLES OF USE:
 -- SELECT rel_description('mytable'); 
 -- SELECT rel_description('public.mytable'); 
 -- SELECT rel_description('otherschema.mytable'); 
 -- SELECT rel_description('mytable', 'otherschema'); 
 -- PS: rel_description('public.mytable', 'otherschema') is a syntax error, 
 --     but not generates exception: returns the same as ('public.mytable') 

NOTES:

  • The comment is produced with the table name, not with an OID... "How to retrieve the comment with the table-name?"

    SELECT obj_description('schemaName.tableName'::regclass, 'pg_class');

  • about obj_description with only object_oid (ex. 393862), obj_description(393862), it is deprecated, the correct is

    SELECT obj_description(393862,'pg_class');

  • As recommended by this answer: "If you want to know which queries does psql run when you do \dt+ or \d+ customers, just launche it with psql -E".

PS: thanks to @a_horse_with_no_name.


Need Your Help

Does 'foldp' violate FP's no mutable state principle?

functional-programming elm

I'm learning about Elm from Seven More Languages in Seven Weeks. The following example confuses me: