Search code examples
mysqlpostgresqldatabase-migration

PostgreSQL query for table column comments?

I have migrated a MySQL database to PostgreSQL & replaced the query

SHOW 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?

Solution

  • 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';