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.attnum

limit 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"
FROM
	information_schema.tables 
WHERE
	table_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.attnum

limit 100   

其他

select tablename from pg_tables where schemaname='public'