I have migrated a MySQL database to PostgreSQL & replaced the querySHOW FULL COLUMNS FROM schema_name.table_name;
with a Postgres equivalent,
SELECT * FROM information_schema.columns WHERE table_schema = 'schema_name' and table_name = 'table_name';
which returns the columns along with their properties however the 'Comment' property that was returned in the MySQL query is not returned in the PostgreSQL query.
Is there a way to query for the comments associated with each column_name?
How about this:
select col_description((table_schema||'.'||table_name)::regclass::oid, ordinal_position) as column_comment
, * from information_schema.columns
WHERE table_schema = 'schema_name'
and table_name = 'table_name';
You know this is shown under \dt+ you can reverse engineer what psql does with -E
-E --echo-hiddenEcho the actual queries generated by \d and other backslash commands. You can use this to study psql's internal operations. This is equivalent to setting the variable ECHO_HIDDEN to on.
psql -d test -E -c'\dt+ foo'
********* QUERY **********
SELECT n.nspname as "Schema",
c.relname as "Name",
CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'm' THEN 'materialized view' WHEN 'i' THEN 'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' WHEN 'f' THEN 'foreign table' END as "Type",
pg_catalog.pg_get_userbyid(c.relowner) as "Owner",
pg_catalog.pg_size_pretty(pg_catalog.pg_table_size(c.oid)) as "Size",
pg_catalog.obj_description(c.oid, 'pg_class') as "Description"
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r','s','')
AND n.nspname !~ '^pg_toast'
AND c.relname ~ '^(foo)$'
AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 1,2;
**************************
You can see here all the information that psql shows. As they say, teach the man to fish... ?
