PostgreSQL直接sql或控制台查询表名称及表结构；获取数据库中所有视图view名；如何查表属于哪个数据库；根据字段名查询所在表

本文主要是介绍PostgreSQL直接sql或控制台查询表名称及表结构；获取数据库中所有视图view名；如何查表属于哪个数据库；根据字段名查询所在表，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

1、查询表结构

psql方式下查询表结构

 \d tablename  

sql方式查看表结构

select a.attnum,
a.attname AS field,
c.relname as relname,
t.typname AS type,
a.attlen AS length,
a.atttypmod AS lengthvar,
a.attnotnull AS notnull,
b.description AS comment
from pg_class c,pg_attribute a
LEFT OUTER JOIN pg_description b ON a.attrelid=b.objoid AND a.attnum = b.objsubid,pg_type t 
WHERE 1=1 
--and c.relname = 'udoc_saldiscount' 
and a.attname = 'productseriescode' 
and a.attnum > 0
and a.attrelid = c.oid
and a.atttypid = t.oid
ORDER BY a.attnumlimit 100

2、查询表名称

psql方式查询表名称

\dt  

sql方式查看表名称

select tablename FROM pg_tables;  -- 
select tablename from pg_tables where schemaname='public'

sql 方式视图名称查询

SELECT   viewname   FROM   pg_views   WHERE     schemaname ='public'

3、表属于哪个数据库

SELECT"table_catalog","table_schema","table_name","table_type","self_referencing_column_name","reference_generation","user_defined_type_catalog","user_defined_type_schema","user_defined_type_name","is_insertable_into","is_typed","commit_action"
FROMinformation_schema.tables 
WHEREtable_name = 'viw_table'

4、根据字段名查询所在表

--  根据字段名查询所在表
SELECT a.attnum as "字段名",
a.attname AS field,
c.relname as "表名",
t.typname AS type,
a.attlen AS length,
a.atttypmod AS lengthvar,
a.attnotnull AS notnull,
b.description AS comment,
c.oid as oid 
FROM pg_class c,pg_attribute a
LEFT OUTER JOIN pg_description b ON a.attrelid=b.objoid AND a.attnum = b.objsubid,pg_type t 
WHERE 1=1 
--and c.relname = 'udoc_saldiscount' 
and a.attname = 'productseriescode' 
and c.relname NOT LIKE 'pg_%'
and a.attnum > 0
and a.attrelid = c.oid
and a.atttypid = t.oid
ORDER BY a.attnumlimit 100   

其他

select tablename from pg_tables where schemaname='public'

这篇关于PostgreSQL直接sql或控制台查询表名称及表结构；获取数据库中所有视图view名；如何查表属于哪个数据库；根据字段名查询所在表的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！

---