-
-
Save alexanderlz/7302623 to your computer and use it in GitHub Desktop.
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' | |
) | |
); |
+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
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.