PostgreSQL实用查询SQL

PostgreSQL实用查询SQL整理了postgresql的系统表关联的常用SQL，如下：--查看数据库select*frompg_database;--查看表空间select*frompg_tablespace;--查看语言select*frompg_language;--查看角色用户select*frompg_user;select*frompg_shadow;select*frompg_roles;--查看会话进程select*frompg_stat_activity;--查看表SELECT*FROMpg_tableswhereschemaname='public';--查看表字段select*frominformation_schema.columnswheretable_schema='public'andtable_name='pf_vip_org';--查看视图select*frompg_viewswhereschemaname='public';select*frominformation_schema.viewswheretable_schema='public';--查看触发器select*frominformation_schema.triggers;--查看序列select*frominformation_schema.sequenceswheresequence_schema='public';--查看约束select*frompg_constraintwherecontype='p'--uunique,pprimary,fforeign,ccheck,ttrigger,xexclusionselecta.relnameastable_name,b.connameasconstraint_name,b.contypeasconstraint_typefrompg_classa,pg_constraintbwherea.oid=b.conrelidanda.relname='cc';--查看索引select*frompg_index;--查看表上存在哪些索引以及大小selectrelname,n.amnameasindex_typefrompg_classm,pg_amnwherem.relam=n.oidandm.oidin(selectb.indexrelidfrompg_classa,pg_indexbwherea.oid=b.indrelidanda.relname='cc');SELECTc.relname,c2.relname,c2.relpages*8assize_kbFROMpg_classc,pg_classc2,pg_indexiWHEREc.relname='cc'ANDc.oid=i.indrelidANDc2.oid=i.indexrelidORDERBYc2.relname;--查看索引定义selectb.indexrelidfrompg_classa,pg_indexbwherea.oid=b.indrelidanda.relname='cc';selectpg_get_indexdef(b.indexrelid);--查看过程函数定义selectoid,*frompg_procwhereproname='insert_platform_action_exist';--oid=24610select*frompg_get_functiondef(24610);--查看表大小(不含索引等信息)selectpg_relation_size('cc');--368640byteselectpg_size_pretty(pg_relation_size('cc'))--360kB--查看DB大小selectpg_size_pretty(pg_database_size('smiletao'));--12M--查看服务器DB运行状态[postgres@eyar~]$pg_ctlstatus-D$PGDATApg_ctl:serverisrunning(PID:2373)/home/postgres/bin/postgres-D/database/pgdata--查看每个DB的使用情况(读,写,缓存,更新,事务等)select*frompg_stat_database--查看索引的使用情况select*frompg_stat_user_indexes;--查看表所对应的数据文件路径与大小SELECTpg_relation_filepath(oid),relpagesFROMpg_classWHERErelname='empsalary';--查看索引与相关字段及大小SELECTn.nspnameASschema_name,r.rolnameastable_owner,bc.relnameAStable_name,ic.relnameASindex_name,a.attnameAScolumn_name,bc.relpages*8asindex_size_kbFROMpg_namespacen,pg_classbc,--baseclasspg_classic,--indexclasspg_indexi,pg_attributea,--attinbasepg_rolesrWHEREbc.relnamespace=n.oidandi.indrelid=bc.oidandi.indexrelid=ic.oidandbc.relowner=r.oidandi.indkey[0]=a.attnumandi.indnatts=1anda.attrelid=bc.oidandn.nspname='public'andbc.relname='cc'ORDERBYschema_name,table_name,index_name,attname;--查看PG锁select*frompg_locks;备注：relpages*8是实际所占磁盘大小--查看表空间大小selectpg_tablespace_size('pg_default');--查看序列与表的对应关系WITHfq_objectsAS(SELECTc.oid,c.relnameASfqname,c.relkind,c.relnameASrelationFROMpg_classcJOINpg_namespacenONn.oid=c.relnamespace),sequencesAS(SELECToid,fqnameFROMfq_objectsWHERErelkind='S'),tablesAS(SELECToid,fqnameFROMfq_objectsWHERErelkind='r')SELECTs.fqnameASsequence,'-'asdepends,t.fqnameAStableFROMpg_dependdJOINsequencessONs.oid=d.objidJOINtablestONt.oid=d.refobjidWHEREd.deptype='a'andt.fqname='cc';