Retrieving Comments from a PostgreSQL DB

select
    c.table_schema,
    c.table_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
);

It all works by oid,

mat=> SELECT c.oid FROM pg_catalog.pg_class c WHERE c.relname = 'customers';
  oid  
-------
 23208
(1 row)

Now, I have the oid for that table, so I can ask :

mat=> select pg_catalog.obj_description(23208);
  obj_description  
-------------------
 Customers
(1 row)

Then, I can ask for the description of the fourth column :

mat=> select pg_catalog.col_description(23208,4);
             col_description             
-----------------------------------------
 Customer codes, CHS, FACTPOST, POWER...
(1 row)

If you want to know which queries does psql run when you do \dt+ or \d+ customers, just run it with -E.

A slight change to one of the other answers which only gives you columns that have comments on them, this gives you all columns whether they have a comment or not.

select c.table_schema, st.relname as TableName, c.column_name, 
pgd.description
from pg_catalog.pg_statio_all_tables as st
inner join information_schema.columns c
on c.table_schema = st.schemaname
and c.table_name = st.relname
left join pg_catalog.pg_description pgd
on pgd.objoid=st.relid
and pgd.objsubid=c.ordinal_position
where st.relname = 'YourTableName';

This works for me using the PostBooks 3.2.2 DB:

select cols.column_name,
(select pg_catalog.obj_description(oid) from pg_catalog.pg_class c where c.relname=cols.table_name) as table_comment
,(select pg_catalog.col_description(oid,cols.ordinal_position::int) from pg_catalog.pg_class c where c.relname=cols.table_name) as column_comment
from information_schema.columns cols
where cols.table_catalog='postbooks' and cols.table_name='apapply'

Regards, Sylnsr

If you just need to show the comments for your columns among other data, you can also use:

\d+ my_table

This answer is a little late, but it popped up on a google search I did to research this problem. We only needed Table descriptions, but the method would be the same for columns. The column descriptions are in the pg_description table also, referenced by objoid.

Add this view:

CREATE OR REPLACE VIEW our_tables AS 
 SELECT c.oid, n.nspname AS schemaname, c.relname AS tablename, d.description,
   pg_get_userbyid(c.relowner) AS tableowner, t.spcname AS "tablespace", 
   c.relhasindex AS hasindexes, c.relhasrules AS hasrules, c.reltriggers > 0 AS hastriggers
   FROM pg_class c
   LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
   LEFT JOIN pg_tablespace t ON t.oid = c.reltablespace
   LEFT JOIN pg_description d ON c.oid = d.objoid
  WHERE c.relkind = 'r'::"char";

ALTER TABLE our_tables OWNER TO postgres;
GRANT SELECT, UPDATE, INSERT, DELETE, REFERENCES, TRIGGER ON TABLE our_tables TO postgres;
GRANT SELECT ON TABLE our_tables TO public;

Then run:

SELECT tablename, description FROM our_tables WHERE schemaname = 'public'

The view is a modified version of the pg_tables view which adds in the description column. You could also monkey around with the view definition to make it a single query.

I accessed table comments like this:

select c.relname table_name, pg_catalog.obj_description(c.oid) as comment from pg_catalog.pg_class c where c.relname = 'table_name';

and column comments thusly:

SELECT 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 and c.table_name = 'table_name' and c.table_schema = 'public');

I asked a similar question about Postgresql comments last month. If you dig through that, you'll come across some Perl code over on my blog that automates the process of extracting a comment.

To pull out the column names of a table, you can use something like the following:

select
     a.attname  as "colname"
    ,a.attrelid as "tableoid"
    ,a.attnum   as "columnoid"
from
    pg_catalog.pg_attribute a
    inner join pg_catalog.pg_class c on a.attrelid = c.oid
where
        c.relname = 'mytable' -- better to use a placeholder
    and a.attnum > 0
    and a.attisdropped is false
    and pg_catalog.pg_table_is_visible(c.oid)
order by a.attnum

You can then use the tableoid,columnoid tuple to extract the comment of each column (see my question).

Ok, so i worked it out to degree...

select col_description(table id, column number)...

ie: select col_description(36698,2);

That worked, but is there an easier way to do this maybe bringing all the comments on all the columns and using the table name instead of the oid???

To display comments from all columns of all table :

SELECT
    cols.table_name,
    cols.column_name, (
    SELECT
        pg_catalog.col_description(c.oid, cols.ordinal_position::int)
    FROM
        pg_catalog.pg_class c
    WHERE
            c.oid = (SELECT ('"' || cols.table_name || '"')::regclass::oid)
      AND c.relname = cols.table_name
) AS column_comment
FROM
    information_schema.columns cols
WHERE
  cols.table_name   IN (SELECT cols.table_name FROM information_schema.columns)
  AND cols.table_catalog = 'your_database_name'
  AND cols.table_schema = 'your_schema_name';

You need to execute this query outside any schema/catalog/db

This query is based on another answer in this question which display comments from one table only

To extend on the response provided by @amxy; I found that adding a schema filter can help in some environments. As I found @amxy's solution didn't work until I added by schema filters

SELECT 
pg_tables.schemaname,
pg_tables.TABLENAME,
pg_attribute.attname AS field,
format_type(pg_attribute.atttypid, NULL) AS "type",
pg_attribute.atttypmod AS len,
(
SELECT col_description(pg_attribute.attrelid, pg_attribute.attnum)) AS  COMMENT,
CASE pg_attribute.attnotnull
    WHEN FALSE THEN 1
    ELSE 0
END AS "notnull",
pg_constraint.conname AS "key", pc2.conname AS ckey,
(
SELECT pg_attrdef.adsrc
FROM pg_attrdef
WHERE pg_attrdef.adrelid = pg_class.oid
    AND pg_attrdef.adnum = pg_attribute.attnum) AS def
FROM pg_tables, pg_class
JOIN pg_attribute
    ON pg_class.oid = pg_attribute.attrelid
    AND pg_attribute.attnum > 0
LEFT JOIN pg_constraint
    ON pg_constraint.contype = 'p'::"char"
    AND pg_constraint.conrelid = pg_class.oid
    AND
(pg_attribute.attnum = ANY (pg_constraint.conkey))
LEFT JOIN pg_constraint AS pc2
    ON pc2.contype = 'f'::"char"
    AND pc2.conrelid = pg_class.oid
    AND (pg_attribute.attnum = ANY (pc2.conkey))
WHERE pg_class.relname = pg_tables.TABLENAME
AND pg_tables.schemaname IN ('op', 'im', 'cs','usr','li')
-- AND pg_tables.tableowner = "current_user"()
    AND pg_attribute.atttypid <> 0::oid
    ---AND TABLENAME='your_table'
ORDER BY pg_tables.schemaname,
pg_tables.TABLENAME ASC;

RESULTS:

SELECT 
    relname table_name,
    obj_description(oid) table_description,
    column_name,
    pgd.description column_description
FROM pg_class
INNER JOIN
    information_schema.columns
        ON table_name = pg_class.relname
LEFT JOIN 
    pg_catalog.pg_description pgd
        ON pgd.objsubid = ordinal_position
WHERE 
    relname = 'your_table_name'