文章目录数据库查看所有数据库查看当前数据库schema查看所有schema查看所有schema-简要信息查看所有schema-详细信息(包含所有者)查看当前schema表查看表结构3大schema查看表注释登录数据库查看当前数据库连接查看当前正在运行sqlABORT 用于退出当前事务。大小写敏感查看所有的表查询所有表名称以及字段含义查看表名和备注查看特定表名备注查看特定表名字段table_catalog 和 table_schema 的区别pg_description表pg_attribute表数据库查看所有数据库SELECTdatnameFROMpg_database;返回结果 test_database# 自己创建的业务数据库postgres# 默认数据库template1# 模板1template0# 模板0也就是说postgrestemplate1template0都是自带的其他的才是业务数据库。查看当前数据库SELECTcurrent_database()schema查看所有schema查看所有schema-简要信息SELECTschema_nameFROMinformation_schema.schemataORDERBYschema_name;查看所有schema-详细信息(包含所有者)SELECTn.nspnameASSchema名称,u.usenameAS所有者FROMpg_namespace nJOINpg_user uONn.nspowneru.usesysidORDERBYn.nspname;查看当前schemaselectcurrent_schema();表查看表结构3大schema2个基本的schemainformation_schemapg_catalog然后还有个默认的schema就是public。创建表时如果不指定的schema那么默认在public下创建表。information_schema和pg_catalog相当于库所以查询的时候后面一定要加点号指定范围以下2种错误写法select*frominformation_schema;错select*frompg_catalog;错 因为pg_catalog相当于是数据库这种语句肯定不对。查看表注释SELECTA.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_typeFROMpg_catalog.pg_attribute AWHERE1 1AND A.attrelid ( SELECT oid FROM pg_class WHERE relname ‘表名’ )AND A.attnum 0AND NOT A.attisdroppedORDER BYA.attnum;pg_attribute 表attrelid 引的class对象的idatttypid 已用的type表的idattnum objsubidpg_description登录数据库psql -h 127.0.0.1 -U dbuser -p 5832 -d database上面的命令的参数含义如下-h 指定服务器-p 指定端口-U 指定用户-d 指定数据库查看当前数据库连接SELECTpg_stat_get_backend_pid(s.backendid)ASprocpid,pg_stat_get_backend_activity(s.backendid)AScurrent_queryFROM(SELECTpg_stat_get_backend_idset()ASbackendid)ASs;查看当前正在运行sqlSELECT 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_queryIDLEORDER BY lap DESC;ABORT 用于退出当前事务。ABORT [ WORK | TRANSACTION ]大小写敏感默认会转换为 小写如果要保留原样要加双引号。查看所有的表selecttablenamefrompg_tableswhereschemanamepublicandposition(_2intablename)0;或select*frompg_tables;查询所有表名称以及字段含义selectc.relname 表名,cast(obj_description(relfilenode,pg_class)asvarchar)名称,a.attname 字段,d.description 字段备注,concat_ws(,t.typname,SUBSTRING(format_type(a.atttypid,a.atttypmod)from\(.*\)))as列类型frompg_class c,pg_attribute a,pg_type t,pg_description dwherea.attnum0anda.attrelidc.oidanda.atttypidt.oidandd.objoida.attrelidandd.objsubida.attnumandc.relnamein(selecttablenamefrompg_tableswhereschemanamepublicandposition(_2intablename)0)orderbyc.relname,a.attnum查看表名和备注selectrelnameastabname,cast(obj_description(relfilenode,pg_class)asvarchar)ascommentfrompg_class cwhererelnamein(selecttablenamefrompg_tableswhereschemanamepublicandposition(_2intablename)0);select*frompg_class;查看特定表名备注selectrelnameastabname,cast(obj_description(relfilenode,pg_class)asvarchar)ascommentfrompg_class cwhererelname表名;查看特定表名字段selecta.attnum,a.attname,concat_ws(,t.typname,SUBSTRING(format_type(a.atttypid,a.atttypmod)from\(.*\)))astype,d.descriptionfrompg_class c,pg_attribute a,pg_type t,pg_description dwherec.relname表名anda.attnum0anda.attrelidc.oidanda.atttypidt.oidandd.objoida.attrelidandd.objsubida.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哦)。查看表的注释selectoidfrompg_classwhererelnamet_user;select*frompg_descriptionwhereobjoid从pg_class查出的oid;pg_attribute表这个表字段比较多常用的字段有字段描述attrelid此字段所属的表,值为对应表的odi也就是pg_class.oid。attname字段名atttypid字段的数据类型,值为pg_type.oidattnum字段的编号普通字段是从1开始计数的。系统字段如oid是任意的负数