Home:ALL Converter>Getting list of table comments in PostgreSQL

Getting list of table comments in PostgreSQL

Ask Time:2011-04-14T21:35:57         Author:Bionicman303

Json Formatter

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!

Author:Bionicman303,eproduced under the CC 4.0 BY-SA copyright license with a link to the original source and this disclaimer.
Link to original article:https://stackoverflow.com/questions/5664094/getting-list-of-table-comments-in-postgresql
a_horse_with_no_name :

All comments are stored in pg_description\n\nTo get the comments on a table, you need to join it to pg_class\n\nAs an alternative you can also use the function obj_description() to retrieve this information:\n\nSELECT obj_description(oid)\nFROM pg_class\nWHERE relkind = 'r'\n\n\nEdit\n\nIn 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",
Ejrr1085 :

You can use pg_catalog.obj_description function and information_schema.tables schema view:\nSELECT t.table_name, pg_catalog.obj_description(pgc.oid, 'pg_class')\nFROM information_schema.tables t\nINNER JOIN pg_catalog.pg_class pgc\nON t.table_name = pgc.relname \nWHERE t.table_type='BASE TABLE'\nAND t.table_schema='public';\n\nFUNCTIONS-INFO-COMMENT-TABLE\nINFORMATION_SCHEMA Support in MySQL, PostgreSQL",