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!
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
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 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.
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!
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';
If you still have tables with mixed case names you can use the following to get a complete list of all tables in a database with their comment, as well as catalog, schema, table type, etc. (tested in PostGIS - pgAdmin 4):
select *,
obj_description((table_schema||'.'||quote_ident(table_name))::regclass)
from information_schema.tables where table_schema <> 'pg_catalog'
