PostgreSQL获取表信息和字段信息

最近用到了postgreSQL查询当前表下的所有字段信息，在此总结一下查询SQL。

注：查询SQL中的tablename需替换成你需要查询的表名。

1.获取数据库中所有table名：

SELECT   tablename   FROM   pg_tables  
WHERE   tablename   NOT   LIKE   'pg%'  
AND tablename NOT LIKE 'sql_%'
ORDER   BY   tablename;

2.获取数据库中所有table名及table的注解信息： 

SELECT   tablename,obj_description(relfilenode,'pg_class')  FROM   pg_tables  a, pg_class b
WHERE   
a.tablename = b.relname
and a.tablename   NOT   LIKE   'pg%'  
AND a.tablename NOT LIKE 'sql_%'
ORDER   BY   a.tablename;

 3.获取数据库中所有table名及table的注解信息： 

SELECT 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 c.relname = 'tablename' and a.attrelid = c.oid and a.attnum>0

 4.查询字段名、字段类型及字段长度和字段注释：

select a.attnum,a.attname,concat_ws('',t.typname,SUBSTRING(format_type(a.atttypid,a.atttypmod) from '(.*)')) as type,d.description from pg_class c, pg_attribute a , pg_type t, pg_description d 
where  c.relname = 'table_name' and a.attnum>0 and a.attrelid = c.oid and a.atttypid = t.oid and  d.objoid=a.attrelid and d.objsubid=a.attnum

最后

以上就是默默大山为你收集整理的PostgreSQL获取表信息和字段信息的全部内容，希望文章能够帮你解决PostgreSQL获取表信息和字段信息所遇到的程序开发问题。

如果觉得靠谱客网站的内容还不错，欢迎将靠谱客网站推荐给程序员好友。