--pg_catalog
SELECT * from pg_class c,pg_attribute a,pg_type t where c.relname='BookKindList' and a.attnum>0 and a.attrelid=c.oid and a.atttypid=t.oid
SELECT a.attname from pg_class c,pg_attribute a,pg_type t where c.relname='BookKindList' and a.attnum>0 and a.attrelid=c.oid and a.atttypid=t.oid
--查询BookKindList表的字段信息 Geovin Du 涂聚文 20150402
SELECT a.attnum,a.attname AS field,t.typname AS type,a.attlen AS length,a.atttypmod AS lengthvar,a.attnotnull AS notnull from pg_class c,pg_attribute a,pg_type t where c.relname='BookKindList' and a.attnum>0 and a.attrelid=c.oid and a.atttypid=t.oid
SELECT * FROM information_schema.columns;
--查表的列表
SELECT * FROM information_schema.columns where table_catalog='geovindu' and table_schema='public' and table_name='bookkindlist';
--
select * from pg_database where datname='bookkindlist';
select datname,dattablespace from pg_database where datname='bookkindlist';
--查看数据库
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 = 'bookkindlist';
--查看视图
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 = 'bookkindlist';
--查看索引
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 = 'bookkindlist');
SELECT c.relname,c2.relname, c2.relpages*8 as size_kb
FROM pg_class c, pg_class c2, pg_index i
WHERE c.relname = 'bookkindlist' 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 = 'bookkindlist';
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('bookkindlist'); --368640 byte
select pg_size_pretty(pg_relation_size('bookkindlist')) --360 kB
--查看DB大小
select pg_size_pretty(pg_database_size('geovindu')); --12M
--查看服务器DB运行状态
[postgres@192.168.20.165 ~]$ 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 = 'bookkindlist';
--查看索引与相关字段及大小
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 = 'bookkindlist'
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 = 'bookkindlist';
--
select * from information_schema.columns where table_catalog= 'geovindu' AND table_name = 'bookkindlist';
select * from pg_description;
---一个查询表结构的SQL
SELECT
col.table_schema ,
col.table_name ,
col.ordinal_position,
col.column_name ,
col.data_type ,
col.character_maximum_length,
col.numeric_precision,
col.numeric_scale,
col.is_nullable,
col.column_default ,
des.description
FROM
information_schema.columns col LEFT JOIN pg_description des
ON col.table_name::regclass = des.objoid
AND col.ordinal_position = des.objsubid
WHERE
table_schema = 'public'
AND table_name = 'bookkindlist'
ORDER BY
ordinal_position;
select * from pg_namespace
select * from pg_class where relname='bookkindlist'
---
SELECT
n.nspname ,
relname
FROM
pg_class c ,
pg_namespace n
WHERE
c.relnamespace = n.oid
AND nspname='public'
AND relkind = 'r'
AND relhassubclass
ORDER BY
nspname ,
relname;
--
select * from information_schema.columns where table_name = 'bookkindlist';
--表结构
select table_schema,table_name,column_name,data_type,column_default,character_maximum_length,is_nullable from information_schema.columns where table_name = 'bookkindlist';
select table_schema,table_name,column_name as FieldName,data_type as FieldType,column_default,character_maximum_length as FieldLength,is_nullable from information_schema.columns where table_name = 'bookkindlist';
--表主键名称
select pg_constraint.conname as pk_name from pg_constraint inner join pg_class on pg_constraint.conrelid = pg_class.oid where pg_class.relname = 'bookkindlist' and pg_constraint.contype='p';
--表主键字段
select pg_constraint.conname as pk_name,pg_attribute.attname as column_name,pg_type.typname as data_type,pg_class.relname as table_name, info.character_maximum_length,info.is_nullable from
pg_constraint inner join pg_class
on pg_constraint.conrelid = pg_class.oid
inner join pg_attribute on pg_attribute.attrelid = pg_class.oid
and pg_attribute.attnum = pg_constraint.conkey[1]
inner join pg_type on pg_type.oid = pg_attribute.atttypid
inner join information_schema.columns as info on info.column_name=pg_attribute.attname
where pg_class.relname = 'bookkindlist'
and pg_constraint.contype='p';
--表主键名称
select conname,conrelid,connamespace from pg_constraint where contype='p';
select * from pg_constraint where contype='p';
---表和表主键名称
select oid,relname from pg_class;
select * from pg_class where relnamespace=2200;
select * from pg_class;
--表
select * from pg_type where typnamespace=2200;
select typnamespace,typname,oid from pg_type where typnamespace=2200;
select * from pg_type where typname='bookkindlist';
-- 主键字段名attname
select * from pg_attribute;
select * from pg_attribute where attstorage='p';
select attrelid,attname,attnum from pg_attribute where attstorage='p';
sql: postgreSQL sql script
原创
©著作权归作者所有:来自51CTO博客作者geovin的原创作品,请联系作者获取转载授权,否则将追究法律责任
提问和评论都可以,用心的回复会被更多人看到
评论
发布评论
相关文章
-
PostgreSQL教程
PostgreSQL教程
PostgreSQL 数据库 运算符 -
PostgreSQL全面剖析
PostgreSQL全面剖析
PostgreSQL全面剖析 -
js中用script 嵌套script块
把script拆开就可以了 新闻 \n"); } //--> #javascript/ajax专栏
html javascript ajax JAVA