I have a table in my database and I hava added comments to each of the column in that table.
comment on column student_details.rollno is 'Roll No';
I have 6 such columns and I have added comments to each of the column in the same way.
I want to retrieve the comments using an sql query(PostgreSQL). I have checked few queries but they were complicated and I couldn't understand.
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'
The above query worked for me but I don't know how. Can somebody explain this to me?