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

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列：

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

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

查看表的注释：

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

pg_attribute表

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

字段	描述
attrelid	此字段所属的表,值为对应表的odi，也就是pg_class.oid。
attname	字段名
atttypid	字段的数据类型,值为pg_type.oid
attnum	字段的编号，普通字段是从1开始计数的。系统字段，如oid，是任意的负数