Retrieving Comments from a PostgreSQL DB

postgresql metadata
Nick picture Nick · Dec 5, 2008 · Viewed 30.7k times · Source

I'm running a project on a Postgres database and need to retrieve the comments on columns within the DB to be used as table headings and such. I have seen that there are a couple of built in functions (pg_description and col_description) but i haven't been able to find examples on how to use them and playing around with them has proved pretty futile.

So I was wondering if any has been able to do this before and if so, how?

Answer

user609896 picture user609896 · Feb 9, 2011 
SELECT c.table_schema,c.table_name,c.column_name,pgd.description
FROM pg_catalog.pg_statio_all_tables as st
  inner join pg_catalog.pg_description pgd on (pgd.objoid=st.relid)
  inner join information_schema.columns c on (pgd.objsubid=c.ordinal_position
    and  c.table_schema=st.schemaname and c.table_name=st.relname);

Related questions

Find dependent objects for a table or view

Background When dropping (or replacing) objects in PostgreSQL, if there are dependencies, the drop will fail (without specifying CASCADE). Problem The error message returned by the database does not list the dependent objects. Example Solution The query might look something …

Read More
How do I find the last time that a PostgreSQL database has been updated?

I am working with a postgreSQL database that gets updated in batches. I need to know when the last time that the database (or a table in the database)has been updated or modified, either will do. I saw that …

Read More
How can I drop all indexes of a table in Postgres?

I keep having this problem: I have like 20 indexes on a table that I need to drop in order to do testing. Dropping the table doesn't drop all of this metadata. There doesn't seem to be a wildcard drop index …

Read More