Retrieving Comments from a PostgreSQL DB

问题:

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?

回答1:

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);

回答2:

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.

回答3:

Take care with schemas, this code consider them:

SELECT
    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_catalog    = 'your_database'
    AND cols.table_name   = 'your_table'
    AND cols.table_schema = 'your_schema';

References:

• Postgresql Document Table and Column Description Comments on Table and Column

• Determining the OID of a table in Postgres 9.1?

回答4:

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

回答5:

Just to be here if somebody will need it.

There are many answers here, but none of them was as simple as I would like it to be. So, based on previous answers and current postgres 9.4, I have created this query:

SELECT 
    obj_description(format('%s.%s',isc.table_schema,isc.table_name)::regclass::oid, 'pg_class') as table_description,
    pg_catalog.col_description(format('%s.%s',isc.table_schema,isc.table_name)::regclass::oid,isc.ordinal_position) as column_description
FROM
    information_schema.columns isc

It fetches table and column descriptions, without any confusing joins and ugly string concatenations.

回答6:

Enhance for @Nick and @mat suggestions: use
SELECT obj_description('schemaName.tableName'::regclass, 'pg_class'); when you have string name (not oid).

To avoid to remember 'pg_class' parameter, and to avoid ugly concatenations at the function calls, as (tname||'.'||schema)::regclass, an useful overload for obj_description:

  CREATE FUNCTION obj_description(
      p_rname text, p_schema text DEFAULT NULL, 
      p_catalname text DEFAULT 'pg_class'
  ) RETURNS text AS $f$
     SELECT obj_description((CASE 
        WHEN strpos($1, '.')>0 OR $2 IS NULL OR $2='' THEN $1
        ELSE $2||'.'||$1
     END)::regclass, $3);
  $f$ LANGUAGE SQL IMMUTABLE;
 -- USAGE: obj_description('mytable') 
 --        SELECT obj_description('s.t'); 
 -- PS: obj_description('s.t', 'otherschema') is a syntax error, 
 --     but not generates exception: returns the same as ('s.t') 

Now is easy to use, because the table name (rname parameter) is a varchar and can be expressed with a separated field for schema name, as in the main tables and queries.

See also "Getting list of table comments in PostgreSQL" or the new pg9.3 Guide

回答7:

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???

回答8:

Cool, that works to bring up a single comment, but is there a way to bring up all the comments from all the columns without multiple select statements or a loop?

And how do you launch this with a statement:

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

Thanks

回答9:

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.

回答10:

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';

回答11:

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

回答12:

I just found this here. It will provide you with all kind of metadata on one specific table (type, default value, not null flag, length, comment, foreign key name, primary key name). It seems to work well.

SELECT 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.tableowner = "current_user"() 
    AND pg_attribute.atttypid <> 0::oid  
    AND tablename='your_table' 
ORDER BY field ASC

Source: http://golden13.blogspot.de/2012/08/how-to-get-some-information-about_7.html

回答13:

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');