One useful feature of SQL Server is the ability to use Extended Properties to add descriptions for tables and columns. I find it helpful to be able to document databases like this, to have these descriptions as part of the database.
Luckily, Postgres has a similar feature, adding comments to tables and columns.
COMMENT ON TABLE public.test_table IS 'Table Comment';
COMMENT ON COLUMN public.test_table.test_table_id IS 'Column Comment';
Here are some queries to retrieve comments for tables and for columns.
information_schema.tables will return tables and views, so I’ve added the table_type = ‘BASE TABLE’ clause to limit the results to tables.
SELECT table_schema, table_name, OBJ_DESCRIPTION(CONCAT(table_schema, '.', table_name)::regclass) as description FROM information_schema.tables WHERE table_catalog = 'test_db' AND table_schema = 'public' AND table_type = 'BASE TABLE' ORDER BY table_name;
SELECT c.table_schema, c.table_name, c.column_name, COL_DESCRIPTION(CONCAT(c.table_schema, '.', c.table_name)::regclass, ordinal_position) as description FROM information_schema.columns as c JOIN information_schema.tables as t ON t.table_catalog = c.table_catalog AND t.table_schema = c.table_schema AND t.table_name = c.table_name WHERE c.table_catalog = 'test_db' AND c.table_schema = 'public' AND t.table_type = 'BASE TABLE' ORDER BY c.table_name, c.ordinal_position;
The information_schema views return other information, like the data type and nullability for columns, so you would have enough information available to generate a data dictionary for the database.