postgresql查看有哪些表，哪些列，注释是什么

谁践踏了优雅 2022-10-28 10:06 243阅读 0赞

### 文章目录 ###

*   *   *  3大schema
         *  查看表注释
         *  登录数据库
         *  查看当前数据库连接
         *  查看当前正在运行sql
         *  ABORT 用于退出当前事务。
         *  大小写敏感
         *  查看所有的表
         *  查询所有表名称以及字段含义
         *  查看表名和备注
         *  查看特定表名备注
         *  查看特定表名字段
         *  table\_catalog 和 table\_schema 的区别
         *  pg\_description表
         *  pg\_attribute表
         *

### 3大schema ###

2个基本的schema：  
information\_schema  
pg\_catalog

然后还有个默认的schema，就是public。  
创建表时，如果不指定的schema，那么默认在public下创建表。

information\_schema和pg\_catalog相当于库，所以查询的时候后面一定要加点号指定范围：

以下2种错误写法：
    select * from information_schema;  错
    select * from pg_catalog;  错
    因为pg_catalog相当于是数据库，这种语句肯定不对。

### 查看表注释 ###

SELECT  
A.attnum,  
( SELECT description FROM pg\_catalog.pg\_description WHERE objoid = A.attrelid AND objsubid = A.attnum ) AS descript,  
A.attname,  
( select typname from pg\_type where oid = A.atttypid) AS type,  
A.atttypmod AS data\_type  
FROM  
pg\_catalog.pg\_attribute A  
WHERE  
1 = 1  
AND A.attrelid = ( SELECT oid FROM pg\_class WHERE relname = ‘表名’ )  
AND A.attnum > 0  
AND NOT A.attisdropped  
ORDER BY  
A.attnum;

pg\_attribute 表  
attrelid 引的class对象的id

atttypid 已用的type表的id

attnum objsubid

pg\_description

### 登录数据库 ###

psql -h 127.0.0.1 -U dbuser -p 5832 -d database  
上面的命令的参数含义如下：  
\-h 指定服务器  
\-p 指定端口  
\-U 指定用户  
\-d 指定数据库

### 查看当前数据库连接 ###

SELECT pg\_stat\_get\_backend\_pid(s.backendid) AS procpid, pg\_stat\_get\_backend\_activity(s.backendid) AS current\_query FROM (SELECT pg\_stat\_get\_backend\_idset() AS backendid) AS s;

### 查看当前正在运行sql ###

SELECT procpid, start, now() - start AS lap, current\_query FROM (SELECT backendid, pg\_stat\_get\_backend\_pid(S.backendid) AS procpid, pg\_stat\_get\_backend\_activity\_start(S.backendid) AS start, pg\_stat\_get\_backend\_activity(S.backendid) AS current\_query FROM (SELECT pg\_stat\_get\_backend\_idset() AS backendid) AS S ) AS S WHERE current\_query <> ‘’ ORDER BY lap DESC;

### ABORT 用于退出当前事务。 ###

ABORT \[ WORK | TRANSACTION \]

### 大小写敏感 ###

默认会转换为 小写，如果要保留原样，要加双引号。

### 查看所有的表 ###

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

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

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 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 ='表名';

### 查看特定表名字段 ###

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

### table\_catalog 和 table\_schema 的区别 ###

table\_catalog 比 table\_schema比和 高一级

### pg\_description表 ###

想要查看注释肯定离不开pg\_description表。  
共有4列：

<table> 
 <thead> 
  <tr> 
   <th align="left">字段</th> 
   <th align="left">描述</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td align="left">objoid</td> 
   <td align="left">所属对象的OID(表oid)</td> 
  </tr> 
  <tr> 
   <td align="left">classoid</td> 
   <td align="left">对象所属系统目录的OID(pg_class的oid)</td> 
  </tr> 
  <tr> 
   <td align="left">objsubid</td> 
   <td align="left">列序号，详细描述我也没看懂：[对于一个表列上的一个注释，这里是列号（objoid和classoid指表本身）。对所有其他对象类型，此列为0。]</td> 
  </tr> 
  <tr> 
   <td align="left">description</td> 
   <td align="left">描述文本</td> 
  </tr> 
 </tbody> 
</table>

注： 根据表名找到oid之后，这里要用objoid关联(不要用classoid哦)。

查看表的注释：

select oid from pg_class where relname='t_user';  
    select * from pg_description where objoid='从pg_class查出的oid';

### pg\_attribute表 ###

这个表字段比较多，常用的字段有：

<table> 
 <thead> 
  <tr> 
   <th align="left">字段</th> 
   <th align="left">描述</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td align="left">attrelid</td> 
   <td align="left">此字段所属的表,值为对应表的odi，也就是pg_class.oid。</td> 
  </tr> 
  <tr> 
   <td align="left">attname</td> 
   <td align="left">字段名</td> 
  </tr> 
  <tr> 
   <td align="left">atttypid</td> 
   <td align="left">字段的数据类型,值为pg_type.oid</td> 
  </tr> 
  <tr> 
   <td align="left">attnum</td> 
   <td align="left">字段的编号，普通字段是从1开始计数的。系统字段，如oid，是任意的负数</td> 
  </tr> 
 </tbody> 
</table>

###  ###