is it possible to retreive the value of a column AND the descirption of each column? I want to see the description behind wach value.


        information_schema.COLUMNS AS c
        LEFT JOIN pg_description as d ON 
        d.objsubid = c.ordinal_position
        TABLE_NAME = <a.tablename> AND column_name = <colname>) as description
FROM table as a
WHERE true;

The table should by look like this:

id: serial 'no description';
col1: int2 'Column One';
col2: int2 'Column Two';


1, 10, 11
2, 20, 21
3, 30, 31

The result should look like this:

1, 10 (column one), 11 (column two)
2, 20 (column one), 21 (column two)
3, 30 (column one), 31 (column two)

Best regards



The only way I can think of is something like this:

select t.id, x.remarks ->> 'id',
       t.col1, x.remarks ->> 'col1',
       t.col2, x.remarks ->> 'col2'
from the_table t
 join lateral ( 
    select jsonb_object_agg(a.attname, col_description(a.attrelid, a.attnum)) as remarks
    from pg_attribute a 
    where a.attrelid = t.tableoid
    and a.attnum > 0
 ) x on true

The inner query aggregates all column descriptions of the table into a single JSON value that can be accessed easily in the outer query.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.