Last active
May 22, 2024 13:26
-
-
Save alexanderlz/7302623 to your computer and use it in GitHub Desktop.
Get column description in postgresql/redshift
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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' | |
) | |
); |
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
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
This code gets all the column descriptions without specifying the table and column names: