PostgreSQL实用查询SQL

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了PostgreSQL实用查询SQL，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含3964字，纯文字阅读大概需要6分钟。

内容图文

PostgreSQL实用查询SQL 整理了postgresql的系统表关联的常用SQL，如下： --查看数据库 www.2cto.com select * from pg_database; --查看表空间 select * from pg_tablespace; --查看语言 select * from pg_language; --查看角色用户 select * from pg_user;

PostgreSQL实用查询SQL

整理了postgresql的系统表关联的常用SQL，如下：

--查看数据库 www.2cto.com

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

d.deptype = 'a' and t.fqname = 'cc';

内容总结

以上是互联网集市为您收集整理的PostgreSQL实用查询SQL全部内容，希望文章能够帮你解决PostgreSQL实用查询SQL所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。

内容备注

版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。

内容手机端