postgressql数据库查询数据库中的所有表及表注释、表的字段、类型、注释更多知识学习：
⼀、查询Postgres数据库中的所有表信息（表名、备注）
SELECT
relname AS tabname,
cast( obj_description ( relfilenode, 'pg_class' ) AS VARCHAR ) AS COMMENT
FROM
pg_class c
WHERE
relkind = 'r'
AND relname NOT LIKE 'pg_%'
AND relname NOT LIKE 'sql_%'
ORDER BY
relname
注意：过滤掉分表：加条件 and relchecks=0 即可
⼆、查询Postgres数据库中的表字段名、类型、注释、注释是否为空
SELECTnamespace是干嘛的
col_description ( a.attrelid, a.attnum ) AS COMMENT,
format_type ( a.atttypid, a.atttypmod ) AS type,
a.attname AS NAME,
a.attnotnull AS notnull
FROM
pg_class AS c,
pg_attribute AS a
WHERE
AND a.attrelid = c.oid
AND a.attnum >0
注意：如果直接复制spl去运⾏的话，如果不能运⾏，请把引号改英⽂的。
;
三、如果⼀中的⽅式查询出来没有表的备注信息，那么请使⽤如下的SQL查询
with tmp_tab as (
select pc.oid as ooid,pn.nspname,pc.*
from pg_class pc
left outer join pg_namespace pn
lnamespace = pn.oid
where 1=1
lkind in ('r')
lnamespace = 2200 -- select pn.oid, pn.* from pg_namespace pn where 1=1      and pc.oid not in (
select inhrelid
from pg_inherits
)
lname not like '%peiyb%'
order lname
),tmp_desc as (
select pd.*
from pg_description pd
where 1=1
and pd.objsubid = 0
--and pd.objoid=168605
)
select t0.*
from (
select tab.nspname,
de.description,
'comment on table '||tab.nspname||'.'||lname||' is '''||de.description||''';'
from tmp_tab tab
left outer join tmp_desc de
id = de.objoid
where 1=1
) t0
where 1=1
and t0.description is not null
order lname
;

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。