PostgreSQL获取table名，字段名

PostgreSQL获取数据库中所有table名：

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

PostgreSQL获取数据库中所有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;

PostgreSQL获取指定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

查询所有表名称以及字段含义

select c.relname 表名,cast(obj_description(relfilenode,'pg_class') as varchar) 名称,a.attname 字段,d.description 字段备注,concat_ws('',t.typname,SUBSTRING(format_type(a.atttypid,a.atttypmod) from '\(.*\)')) as 列类型 from pg_class c,pg_attribute a,pg_type t,pg_description d
where a.attnum>0 and a.attrelid=c.oid and a.atttypid=t.oid and d.objoid=a.attrelid and d.objsubid=a.attnum
and c.relname in (select tablename from pg_tables where schemaname='public' and position('_2' in tablename)=0) order by c.relname,a.attnum

查看所有表名

select tablename from pg_tables where schemaname='public' and position('_2' in tablename)=0;
 
select * from pg_tables;

查看表名和备注

select relname as tabname,cast(obj_description(relfilenode,'pg_class') as varchar) as comment from pg_class c
where relname in (select tablename from pg_tables where schemaname='public' and position('_2' in tablename)=0);
 
select * from pg_class;

查看特定表名备注

select relname as tabname,
cast(obj_description(relfilenode,'pg_class') as varchar) as comment from pg_class c 
where relname ='tbl_alarm';

查看特定表名字段

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='tbl_alarm' and a.attnum>0 and a.attrelid=c.oid and a.atttypid=t.oid and d.objoid=a.attrelid and d.objsubid=a.attnum;