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!
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'
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')
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
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.