PostgreSQL在（DBeaver）查看表结构

宋------

已于 2023-03-14 09:52:50 修改

阅读量5k

点赞数

CC 4.0 BY-SA版权

文章标签： sql 数据库 postgresql

于 2022-09-23 17:21:47 首次发布

本文链接：https://blog.csdn.net/wenwen1542/article/details/127009526

本文介绍了如何在DBeaver数据库管理工具中查询PostgreSQL数据库的表结构，包括查看所有表、特定表的详细信息、字段、类型以及描述。通过SQL查询语句，可以获取到表的字段名、类型、描述等关键信息，对于数据库管理和开发工作非常实用。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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
;