PostgreSQL实用SQL

--查看数据库select * from pg_database;--查看表空间select * from pg_tablespace;--查看语言select * from pg_language;--查看角色用户select * from pg_user;select * from pg_shadow;select * from pg_roles;--查看会话进程select * from pg_stat_activity;--查看表SELECT * FROM pg_tables where schemaname = 'public';--查看表字段select * from information_schema.columns where table_schema = 'public' and table_name = 'pf_vip_org';--查看视图select * from pg_views where schemaname = 'public';select * from information_schema.views where table_schema = 'public';--查看触发器select * from information_schema.triggers;--查看序列select * from information_schema.sequences where sequence_schema = 'public'; --查看约束select * from pg_constraint where contype = 'p'  --u unique,p primary,f foreign,c check,t trigger,x exclusionselect a.relname as table_name,b.conname as constraint_name,b.contype as constraint_type from pg_class a,pg_constraint b where a.oid = b.conrelid and a.relname = 'cc';--查看索引select * from pg_index ;--查看表上存在哪些索引以及大小select relname,n.amname as index_type from pg_class m,pg_am n where m.relam = n.oid and m.oid in (select b.indexrelid from pg_class a,pg_index b where a.oid = b.indrelid and a.relname = 'cc');SELECT c.relname,c2.relname, c2.relpages*8 as size_kbFROM pg_class c, pg_class c2, pg_index iWHERE c.relname = 'cc' ANDc.oid = i.indrelid ANDc2.oid = i.indexrelidORDER BY c2.relname; --查看索引定义select b.indexrelid from pg_class a,pg_index b where a.oid = b.indrelid and a.relname = 'cc';select pg_get_indexdef(b.indexrelid);--查看过程函数定义select oid,* from pg_proc where proname = 'insert_platform_action_exist'; --oid = 24610select * from pg_get_functiondef(24610);--查看表大小(不含索引等信息)select pg_relation_size('cc');                         --368640 byteselect pg_size_pretty(pg_relation_size('cc'))   --360 kB--查看DB大小select pg_size_pretty(pg_database_size('smiletao'));   --12M--查看服务器DB运行状态[postgres@eyar ~]$ pg_ctl status -D $PGDATApg_ctl: server is running (PID: 2373)/home/postgres/bin/postgres "-D" "/database/pgdata" --查看每个DB的使用情况(读,写,缓存,更新,事务等)select * from pg_stat_database--查看索引的使用情况select * from pg_stat_user_indexes;--查看表所对应的数据文件路径与大小SELECT pg_relation_filepath(oid), relpages FROM pg_class WHERE relname = 'empsalary';--查看索引与相关字段及大小 SELECT n.nspname AS schema_name,        r.rolname as table_owner,       bc.relname AS table_name,       ic.relname AS index_name,       a.attname  AS column_name,       bc.relpages*8 as index_size_kb       FROM pg_namespace n,       pg_class bc,             -- base class       pg_class ic,             -- index class       pg_index i,       pg_attribute a,           -- att in base       pg_roles r  WHERE bc.relnamespace = n.oid     and i.indrelid = bc.oid     and i.indexrelid = ic.oid     and bc.relowner = r.oid     and i.indkey[0] = a.attnum     and i.indnatts = 1     and a.attrelid = bc.oid     and n.nspname = 'public'     and bc.relname = 'cc'  ORDER BY schema_name, table_name, index_name, attname;--查看PG锁select * from pg_locks;备注：relpages*8 是实际所占磁盘大小--查看表空间大小select pg_tablespace_size('pg_default');--查看序列与表的对应关系  WITH fq_objects AS (SELECT c.oid,c.relname AS fqname ,                           c.relkind, c.relname AS relation                    FROM pg_class c JOIN pg_namespace n ON n.oid = c.relnamespace ),     sequences AS (SELECT oid,fqname FROM fq_objects WHERE relkind = 'S'),      tables    AS (SELECT oid, fqname FROM fq_objects WHERE relkind = 'r' )          SELECT       s.fqname AS sequence,       '->' as depends,       t.fqname AS table      FROM       pg_depend d JOIN sequences s ON s.oid = d.objid                  JOIN tables t ON t.oid = d.refobjid           WHERE       d.deptype = 'a' and t.fqname = 'cc';