Get column description in postgresql/redshift

col_desc.sql

SELECT description FROM pg_catalog.pg_description WHERE objsubid = 
        (
            SELECT ordinal_position FROM  information_schema.columns WHERE table_name='YOUR_TABLE_NAME' AND column_name='YOUR_COLUMN_NAME'
        ) 
        and objoid = 
        (
            SELECT oid FROM pg_class WHERE relname = 'YOUR_TABLE_NAME' AND relnamespace = 
                        (
                            SELECT oid FROM pg_catalog.pg_namespace WHERE nspname = 'public'
                        )
        );

Fabulous !
Thank you so much for this, I was kinda getting desperate after I reading "Comments cannot be retrieved directly by using SQL commands." on the aws documentation page.

+1
Thanks for this!
Can never quite trust the AWS docs!

select c.table_schema,c.table_name,c.column_name,c.ordinal_position,c.column_default,c.data_type,d.description
from information_schema.columns c
inner join pg_class c1
on c.table_name=c1.relname
inner join pg_catalog.pg_namespace n
on c.table_schema=n.nspname
and c1.relnamespace=n.oid
left join pg_catalog.pg_description d
on d.objsubid=c.ordinal_position
and d.objoid=c1.oid
where c.table_name='YOUR_TABLE_NAME'
and c.table_schema='YOUR_SCHEMA'

This code gets all the column descriptions without specifying the table and column names:

WITH tables AS (SELECT oid, relname AS table FROM pg_class), columns AS (SELECT ordinal_position AS objsubid, table_name AS table, column_name AS column FROM information_schema.columns) SELECT t.table, c.COLUMN, d.description FROM pg_catalog.pg_description d LEFT JOIN tables t ON d.objoid = t.oid LEFT JOIN columns c ON d.objsubid = c.objsubid AND t.table = c.table WHERE d.objsubid > 0

get table and all it's columns comments (works on postgresql too):

SELECT COALESCE(c.column_name, 'table') AS col, d.description
FROM pg_catalog.pg_description AS d
LEFT JOIN information_schema.columns AS c ON
  c.ordinal_position = d.objsubid
  AND c.table_name = 'YOUR_TABLE_NAME'
WHERE objoid = (
  SELECT oid
  FROM pg_class
  WHERE relname = 'YOUR_TABLE_NAME' AND relnamespace = (
    SELECT oid FROM pg_catalog.pg_namespace WHERE nspname = 'public'
  )
)
;

This thread helped me a lot to arrive at the following query for Redshift. It pulls all column and table descriptions that are not null.

WITH tables AS (
    SELECT c.oid,
           ns.nspname as schema_name,
           c.relname as table_name,
           d.description as table_description
    FROM pg_catalog.pg_class AS c
    JOIN pg_catalog.pg_namespace AS ns
      ON c.relnamespace = ns.oid
    LEFT JOIN pg_catalog.pg_description d
      on c.oid = d.objoid
     and d.objsubid = 0
     WHERE ns.nspname not in ('pg_catalog') 
)
SELECT t.oid,
       c.table_schema as schema_name,
       c.table_name AS table_name,
       t.table_description,
       c.column_name AS column_name,
       c.ordinal_position,
       d.description as column_description
from tables t
join information_schema.columns c
    on c.table_schema = t.schema_name
   and c.table_name = t.table_name
left join pg_catalog.pg_description d
   ON d.objoid = t.oid
  AND d.objsubid = c.ordinal_position
  AND d.objsubid > 0
where 1=1
and coalesce(table_description, column_description) is not null
order by
    t.schema_name,
    t.table_name,
    c.ordinal_position

This thread helped me a lot too. Studying a little the query from @johnson-jay-l , I was able to add the table owner as well:

WITH tables AS (
    SELECT c.oid,
           ns.nspname as schema_name,
           c.relname as table_name,
           d.description as table_description,
           pg_get_userbyid(c.relowner) AS table_owner
    FROM pg_catalog.pg_class AS c
    JOIN pg_catalog.pg_namespace AS ns
      ON c.relnamespace = ns.oid
    LEFT JOIN pg_catalog.pg_description d
      on c.oid = d.objoid
     and d.objsubid = 0
     WHERE ns.nspname not in ('pg_catalog') 
)
SELECT t.oid,
       c.table_schema as schema_name,
       c.table_name AS table_name,
       t.table_description,
       c.column_name AS column_name,
       c.ordinal_position,
       d.description as column_description,
       t.table_owner
from tables t
join information_schema.columns c
    on c.table_schema = t.schema_name
   and c.table_name = t.table_name
left join pg_catalog.pg_description d
   ON d.objoid = t.oid
  AND d.objsubid = c.ordinal_position
  AND d.objsubid > 0
where 1=1
order by
    t.schema_name,
    t.table_name,
    c.ordinal_position