PostgreSQL实用SQL_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; --查看会话进程 sele --查看数据库
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 exclusion

select 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_kb
FROM pg_class c,pg_class c2,pg_index i
WHERE c.relname = 'cc' AND
c.oID = i.indrelID AND
c2.oID = i.indexrelID
ORDER 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 = 24610
select * from pg_get_functiondef(24610);

--查看表大小(不含索引等信息)
select pg_relation_size('cc'); --368640 byte
select 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 $PGDATA
pg_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
@H_419_213@ d.deptype = 'a' and t.fqname = 'cc';

@H_419_213@

@H_419_213@zhima=# select regexp_split_to_table('a,b,c,d',',');
regexp_split_to_table
-----------------------
a
b
c
d
(4 rows)

@H_419_213@

zhima=# select unnest(string_to_array('1,2,3,4,5,6','))

zhima-# ;

unnest

--------

(6 rows)

zhima=#

总结

以上是内存溢出为你收集整理的PostgreSQL实用SQL全部内容，希望文章能够帮你解决PostgreSQL实用SQL所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。