Getting list of table comments in PostgreSQL

The main problem with "show comments" is to remember the name of specific fucntions, catalog names, etc. to retrieve the comment... Or its pages on the Guide. At this answer we solve in 2 ways:
by a summary of the ordinary way (the pg-way) to show comments;
and by offering shortcut functions, to reduce the "remember problem".

The pg-way

The simplest, on psql, is to use \dt+ to show table comments and \d+ to show column comments. Some for function comments?

To get on SQL, and for people that remember all parameters, the pg-way is to use the obj_description() function (Guide) in conjunction with adequate reg-type:

  • Function: select obj_description('mySchema.myFunction'::regproc, 'pg_proc')

  • Table or View:
    ("... and most everything else that has columns or is otherwise similar to a table",guide)
    select obj_description('mySchema.myClass'::regclass, 'pg_class')

  • other generic: select obj_description('mySchema.myObject'::regName, pg_regName), where regName is 1 in 10 of datatype-oid references Guide, and pg_regName is the same replacing prefix reg by prefix pg_.

  • other specific: similar select obj_description('schema.myObject'::regName, catalog_name), where catalog_name is to be more specific about a (1 in 95) key-word at catalogs Guide. It can reduce some "namespace pollution". For example pg_proc for functions, pg_aggregate for aggregate functions.

  • to get comment for a shared database object, analog but using the function shobj_description() (same page Guide).

  • Column: select col_description('mySchema.myObject'::regClass, column_number), where column_number is the column's ordinal position (at the CREATE TABLE).
    No column-name... See col_description(table,column_name) complement bellow.

IMPORTANT: the use of same reg-type and _catalog_name_ (e. g. ::regclass and pg_class) seems redundant and sometimes obj_description('obj'::regObj) works fine, with only reg-type! ...But, as the Guide say:

it is deprecated since there is no guarantee that OIDs are unique across different system catalogs; therefore, the wrong comment might be returned.

Shortcut functions to get comments

if you are finding it difficult to remember all the type-casts and parameters, the best is to adopt a new and simplest function to retrieve comments.

 CREATE FUNCTION rel_description(
     p_relname text, p_schemaname text 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')

We need also something less ugly to show column comments. There are no kind of pg_get_serial_sequence() function to get ordinal position of a column from its name. The native
col_description('mySchema.myObject'::regClass, column_number)
needs a complement: 

 CREATE FUNCTION col_description(
    p_relname text,  -- table name or schema.table 
    p_colname text,   -- table's column name
    p_database text DEFAULT NULL -- NULL for current
 ) RETURNS text AS $f$
    WITH r AS (
      SELECT CASE WHEN array_length(x,1)=1 THEN array['public',x[1]] ELSE x END
      FROM regexp_split_to_array(p_relname,'\.') t(x)
     ) 
    SELECT col_description(p_relname::regClass, ordinal_position)
    FROM r, information_schema.columns i
    WHERE i.table_catalog = CASE 
        WHEN $3 IS NULL THEN current_database() ELSE $3 
      END and i.table_schema  = r.x[1]
      and i.table_name    = r.x[2]
      and i.column_name = p_colname
 $f$ LANGUAGE SQL;
 -- SELECT col_description('tableName','colName'); 
 -- SELECT col_description('schemaName.tableName','colName','databaseName);

NOTES:

  • 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".

  • It is possible to express multiline comment, using any multiline string (with E\n or $$...$$)...
    But you can't apply trim() or use another dynamic aspect. Must use dynamic SQL on COMMENT clause for it.

  • No comments to see? PostgreSQL programmers not use COMMENT clause because it is ugly to use: there are no syntax to add comment on CREATE TABLE or on CREATE FUNCTION; and there are no good IDE to automatize it.

  • The modern http://postgREST.org/ interface show comments on the Web!


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


You can use pg_catalog.obj_description function and information_schema.tables schema view:

SELECT t.table_name, pg_catalog.obj_description(pgc.oid, 'pg_class')
FROM information_schema.tables t
INNER JOIN pg_catalog.pg_class pgc
ON t.table_name = pgc.relname 
WHERE t.table_type='BASE TABLE'
AND t.table_schema='public';

FUNCTIONS-INFO-COMMENT-TABLE

INFORMATION_SCHEMA Support in MySQL, PostgreSQL

Tags:

Sql

Postgresql

Recent Posts