PostgreSQL在（DBeaver）查看表结构

PostgreSQL在（DBeaver）查看字段类型(表结构)

--查看所有表
select * from pg_catalog.pg_class where  relname  = 'TABLE_NAME';

select * from pg_catalog.pg_tables where tablename = 'TABLE_NAME';

--查看字段
select * from pg_catalog.pg_attribute;
--查看类型
select * from pg_catalog.pg_type;

--查看描述
select * from pg_catalog.pg_description;

--查找库的oid
select * from pg_catalog.pg_namespace where nspname ='TABLE_NAMESPACE';


--关联查询表结构

--如果不限制name_space会查到不同数据库下的同名表,查出的字段会重复
select
a.attnum as attnum,
c.relname as relname,
a.attname as attname,
c.oid,
a.attrelid,
cast(obj_description(c.oid) as varchar) as obj_description,
concat_ws('',t.typname,substring(format_type(a.atttypid,a.atttypmod) from '\(.*\)')) as type,
d.description as description
from  pg_catalog.pg_attribute a
left join pg_catalog .pg_class c
on a.attrelid = c.oid
left join pg_catalog.pg_type t
on t.oid=a.atttypid
left join pg_catalog.pg_description d
on d.objoid = a.attrelid 
and d.objsubid=a.attnum
where c.relname = 'TABLE_NAME' and a.attnum >0
and c.relnamespace = (select b."oid" from pg_catalog.pg_tables  a
left join pg_catalog.pg_namespace b
on a.schemaname=b.nspname
where tablename = 'TABLE_NAME'
and a.schemaname='TABLE_NAMESPACE') 
order by c.relname desc ,a.attnum asc
;