表列注释的PostgreSQL查询?

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.

我已经将一个MySQL数据库迁移到PostgreSQL，并替换了从schema_name.table_name中显示完整列的查询。与Postgres等价，从information_schema中选择*。table_schema = 'schema_name'和table_name = 'table_name'的列;但是，在MySQL查询中返回的“Comment”属性并没有返回到PostgreSQL查询中。

Is there a way to query for the comments associated with each column_name?

是否有方法查询与每个column_name关联的注释?

2 个解决方案

#1

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

#2

You know this is shown under \dt+ you can reverse engineer what psql does with -E

你知道这显示在\dt+你可以反向工程师psql对-E做什么。

-E --echo-hidden Echo 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.

-E——回波隐藏的回声，是由\d和其他反斜杠命令生成的实际查询。您可以使用它来研究psql的内部操作。这相当于将变量ECHO_HIDDEN设置为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... ?

这里可以看到psql显示的所有信息。正如他们所说，教他钓鱼……吗?

#1