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表的字段信息 20150402 Geovin Du 涂聚文
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;
---一个查询表结构的ＳＱＬ
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,udt_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,udt_name,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';
---http://www.alberton.info/postgresql_meta_info.html
--列出所有数据库中的表名 Detailed table field info
SELECT relname
FROM pg_class
WHERE relname !~ '^(pg_|sql_)'
AND relkind = 'r';
--
SELECT c.relname AS "Name"
FROM pg_class c, pg_user u
WHERE c.relowner = u.usesysid
AND c.relkind = 'r'
AND NOT EXISTS (
SELECT 1
FROM pg_views
WHERE viewname = c.relname
)
AND c.relname !~ '^(pg_|sql_)'
UNION
SELECT c.relname AS "Name"
FROM pg_class c
WHERE c.relkind = 'r'
AND NOT EXISTS (
SELECT 1
FROM pg_views
WHERE viewname = c.relname
)
AND NOT EXISTS (
SELECT 1
FROM pg_user
WHERE usesysid = c.relowner
)
AND c.relname !~ '^pg_';
-->
-- using INFORMATION_SCHEMA:
SELECT table_name
FROM information_schema.tables
WHERE table_type = 'BASE TABLE'
AND table_schema NOT IN
('pg_catalog', 'information_schema');
--列出所有视图
-- with postgresql 7.2:
SELECT viewname
FROM pg_views
WHERE viewname !~ '^pg_';
-- with postgresql 7.4 and later:
SELECT viewname
FROM pg_views
WHERE schemaname NOT IN
('pg_catalog', 'information_schema')
AND viewname !~ '^pg_';
-- using INFORMATION_SCHEMA:
SELECT table_name
FROM information_schema.tables
WHERE table_type = 'VIEW'
AND table_schema NOT IN
('pg_catalog', 'information_schema')
AND table_name !~ '^pg_';
-- or
SELECT table_name
FROM information_schema.views
WHERE table_schema NOT IN ('pg_catalog', 'information_schema')
AND table_name !~ '^pg_';
--show only the VIEWs referencing a given table
SELECT viewname
FROM pg_views
NATURAL JOIN pg_tables
WHERE tablename ='bookinfolist';
--列出所有用户
SELECT usename FROM pg_user;
--列出某表中得所有字段 List INDICES
SELECT a.attname
FROM pg_class c, pg_attribute a, pg_type t
WHERE c.relname = 'bookinfolist'
AND a.attnum > 0
AND a.attrelid = c.oid
AND a.atttypid = t.oid
-- with INFORMATION_SCHEMA:
SELECT column_name
FROM information_schema.columns
WHERE table_name = 'bookinfolist';
--列出某表字段的信息 Detailed INDEX info
SELECT a.attnum AS ordinal_position,
a.attname AS column_name,
t.typname AS data_type,
a.attlen AS character_maximum_length,
a.atttypmod AS modifier,
a.attnotnull AS notnull,
a.atthasdef AS hasdefault
FROM pg_class c,
pg_attribute a,
pg_type t
WHERE c.relname = 'bookinfolist'
AND a.attnum > 0
AND a.attrelid = c.oid
AND a.atttypid = t.oid
ORDER BY a.attnum;
-- with INFORMATION_SCHEMA:
SELECT ordinal_position,
column_name,
data_type,
column_default,
is_nullable,
character_maximum_length,
numeric_precision
FROM information_schema.columns
WHERE table_name = 'bookinfolist'
ORDER BY ordinal_position;
--List INDICES
SELECT relname
FROM pg_class
WHERE oid IN (
SELECT indexrelid
FROM pg_index, pg_class
WHERE pg_class.relname='bookinfolist'
AND pg_class.oid=pg_index.indrelid
AND indisunique != 't'
AND indisprimary != 't'
);
--列出表的索引信息 List CONSTRAINTs
SELECT relname, indkey
FROM pg_class, pg_index
WHERE pg_class.oid = pg_index.indexrelid
AND pg_class.oid IN (
SELECT indexrelid
FROM pg_index, pg_class
WHERE pg_class.relname='bookinfolist'
AND pg_class.oid=pg_index.indrelid
AND indisunique != 't'
AND indisprimary != 't'
);
SELECT t.relname, a.attname, a.attnum
FROM pg_index c
LEFT JOIN pg_class t
ON c.indrelid = t.oid
LEFT JOIN pg_attribute a
ON a.attrelid = t.oid
AND a.attnum = ANY(indkey)
WHERE t.relname = 'bookinfolist'
AND a.attnum = 6; -- this is the index key
--列出表的约束
SELECT c.conname AS constraint_name,
CASE c.contype
WHEN 'c' THEN 'CHECK'
WHEN 'f' THEN 'FOREIGN KEY'
WHEN 'p' THEN 'PRIMARY KEY'
WHEN 'u' THEN 'UNIQUE'
END AS "constraint_type",
CASE WHEN c.condeferrable = 'f' THEN 0 ELSE 1 END AS is_deferrable,
CASE WHEN c.condeferred = 'f' THEN 0 ELSE 1 END AS is_deferred,
t.relname AS table_name,
array_to_string(c.conkey, ' ') AS constraint_key,
CASE confupdtype
WHEN 'a' THEN 'NO ACTION'
WHEN 'r' THEN 'RESTRICT'
WHEN 'c' THEN 'CASCADE'
WHEN 'n' THEN 'SET NULL'
WHEN 'd' THEN 'SET DEFAULT'
END AS on_update,
CASE confdeltype
WHEN 'a' THEN 'NO ACTION'
WHEN 'r' THEN 'RESTRICT'
WHEN 'c' THEN 'CASCADE'
WHEN 'n' THEN 'SET NULL'
WHEN 'd' THEN 'SET DEFAULT'
END AS on_delete,
CASE confmatchtype
WHEN 'u' THEN 'UNSPECIFIED'
WHEN 'f' THEN 'FULL'
WHEN 'p' THEN 'PARTIAL'
END AS match_type,
t2.relname AS references_table,
array_to_string(c.confkey, ' ') AS fk_constraint_key
FROM pg_constraint c
LEFT JOIN pg_class t ON c.conrelid = t.oid
LEFT JOIN pg_class t2 ON c.confrelid = t2.oid
WHERE t.relname = 'bookinfolist'
AND c.conname = 'bookinfolist_pkey';
-- with INFORMATION_SCHEMA:
--列出表的约束和主键列名 Detailed CONSTRAINT info
SELECT tc.constraint_name,
tc.constraint_type,
tc.table_name,
kcu.column_name,
tc.is_deferrable,
tc.initially_deferred,
rc.match_option AS match_type,
rc.update_rule AS on_update,
rc.delete_rule AS on_delete,
ccu.table_name AS references_table,
ccu.column_name AS references_field
FROM information_schema.table_constraints tc
LEFT JOIN information_schema.key_column_usage kcu
ON tc.constraint_catalog = kcu.constraint_catalog
AND tc.constraint_schema = kcu.constraint_schema
AND tc.constraint_name = kcu.constraint_name
LEFT JOIN information_schema.referential_constraints rc
ON tc.constraint_catalog = rc.constraint_catalog
AND tc.constraint_schema = rc.constraint_schema
AND tc.constraint_name = rc.constraint_name
LEFT JOIN information_schema.constraint_column_usage ccu
ON rc.unique_constraint_catalog = ccu.constraint_catalog
AND rc.unique_constraint_schema = ccu.constraint_schema
AND rc.unique_constraint_name = ccu.constraint_name
WHERE tc.table_name = 'bookinfolist'
AND tc.constraint_name = 'bookinfolist_pkey';
--列出所有主键约束序列 List sequences
SELECT relname
FROM pg_class
WHERE relkind = 'S'
AND relnamespace IN (
SELECT oid
FROM pg_namespace
WHERE nspname NOT LIKE 'pg_%'
AND nspname != 'information_schema'
);
--列出所有触发器 List TRIGGERs
SELECT trg.tgname AS trigger_name
FROM pg_trigger trg, pg_class tbl
WHERE trg.tgrelid = tbl.oid
AND tbl.relname !~ '^pg_';
-- or
SELECT tgname AS trigger_name
FROM pg_trigger
WHERE tgname !~ '^pg_';
-- with INFORMATION_SCHEMA:
SELECT DISTINCT trigger_name
FROM information_schema.triggers
WHERE trigger_schema NOT IN
('pg_catalog', 'information_schema');
---List only the triggers for a given table:
SELECT trg.tgname AS trigger_name
FROM pg_trigger trg, pg_class tbl
WHERE trg.tgrelid = tbl.oid
AND tbl.relname = 'bookinfolist';
-- with INFORMATION_SCHEMA:
SELECT DISTINCT trigger_name
FROM information_schema.triggers
WHERE event_object_table = 'bookinfolist'
AND trigger_schema NOT IN
('pg_catalog', 'information_schema');
SELECT usename FROM pg_user;
--列出所有触发器的信息 Detailed TRIGGER info
SELECT trg.tgname AS trigger_name,
tbl.relname AS table_name,
p.proname AS function_name,
CASE trg.tgtype & cast(2 as int2)
WHEN 0 THEN 'AFTER'
ELSE 'BEFORE'
END AS trigger_type,
CASE trg.tgtype & cast(28 as int2)
WHEN 16 THEN 'UPDATE'
WHEN 8 THEN 'DELETE'
WHEN 4 THEN 'INSERT'
WHEN 20 THEN 'INSERT, UPDATE'
WHEN 28 THEN 'INSERT, UPDATE, DELETE'
WHEN 24 THEN 'UPDATE, DELETE'
WHEN 12 THEN 'INSERT, DELETE'
END AS trigger_event,
CASE trg.tgtype & cast(1 as int2)
WHEN 0 THEN 'STATEMENT'
ELSE 'ROW'
END AS action_orientation
FROM pg_trigger trg,
pg_class tbl,
pg_proc p
WHERE trg.tgrelid = tbl.oid
AND trg.tgfoid = p.oid
AND tbl.relname !~ '^pg_';
-- with INFORMATION_SCHEMA:
SELECT *
FROM information_schema.triggers
WHERE trigger_schema NOT IN
('pg_catalog', 'information_schema');
--列出所有函数List FUNCTIONs
SELECT proname
FROM pg_proc pr,
pg_type tp
WHERE tp.oid = pr.prorettype
AND pr.proisagg = FALSE
AND tp.typname <> 'trigger'
AND pr.pronamespace IN (
SELECT oid
FROM pg_namespace
WHERE nspname NOT LIKE 'pg_%'
AND nspname != 'information_schema'
);
-- with INFORMATION_SCHEMA:
SELECT routine_name
FROM information_schema.routines
WHERE specific_schema NOT IN
('pg_catalog', 'information_schema')
AND type_udt_name != 'trigger';
---Albe Laurenz sent me the following function that is even more informative: for a function name and schema, it selects the position in the argument list, the direction, the name and the data-type of each argument. This procedure requires PostgreSQL 8.1 or later.
CREATE OR REPLACE FUNCTION public.function_args(
IN funcname character varying,
IN schema character varying,
OUT pos integer,
OUT direction character,
OUT argname character varying,
OUT datatype character varying)
RETURNS SETOF RECORD AS $$DECLARE
rettype character varying;
argtypes oidvector;
allargtypes oid[];
argmodes "char"[];
argnames text[];
mini integer;
maxi integer;
BEGIN
/* get object ID of function */
SELECT INTO rettype, argtypes, allargtypes, argmodes, argnames
CASE
WHEN pg_proc.proretset
THEN 'setof ' || pg_catalog.format_type(pg_proc.prorettype, NULL)
ELSE pg_catalog.format_type(pg_proc.prorettype, NULL) END,
pg_proc.proargtypes,
pg_proc.proallargtypes,
pg_proc.proargmodes,
pg_proc.proargnames
FROM pg_catalog.pg_proc
JOIN pg_catalog.pg_namespace
ON (pg_proc.pronamespace = pg_namespace.oid)
WHERE pg_proc.prorettype <> 'pg_catalog.cstring'::pg_catalog.regtype
AND (pg_proc.proargtypes[0] IS NULL
OR pg_proc.proargtypes[0] <> 'pg_catalog.cstring'::pg_catalog.regtype)
AND NOT pg_proc.proisagg
AND pg_proc.proname = funcname
AND pg_namespace.nspname = schema
AND pg_catalog.pg_function_is_visible(pg_proc.oid);
/* bail out if not found */
IF NOT FOUND THEN
RETURN;
END IF;
/* return a row for the return value */
pos = 0;
direction = 'o'::char;
argname = 'RETURN VALUE';
datatype = rettype;
RETURN NEXT;
/* unfortunately allargtypes is NULL if there are no OUT parameters */
IF allargtypes IS NULL THEN
mini = array_lower(argtypes, 1); maxi = array_upper(argtypes, 1);
ELSE
mini = array_lower(allargtypes, 1); maxi = array_upper(allargtypes, 1);
END IF;
IF maxi < mini THEN RETURN; END IF;
/* loop all the arguments */
FOR i IN mini .. maxi LOOP
pos = i - mini + 1;
IF argnames IS NULL THEN
argname = NULL;
ELSE
argname = argnames[pos];
END IF;
IF allargtypes IS NULL THEN
direction = 'i'::char;
datatype = pg_catalog.format_type(argtypes[i], NULL);
ELSE
direction = argmodes[i];
datatype = pg_catalog.format_type(allargtypes[i], NULL);
END IF;
RETURN NEXT;
END LOOP;
RETURN;
END;$$ LANGUAGE plpgsql STABLE STRICT SECURITY INVOKER;
COMMENT ON FUNCTION public.function_args(character varying, character
varying)
IS $$For a function name and schema, this procedure selects for each
argument the following data:
- position in the argument list (0 for the return value)
- direction 'i', 'o', or 'b'
- name (NULL if not defined)
- data type$$;
--列出所有存储过程 Show PROCEDURE definition
SELECT p.proname AS procedure_name,
p.pronargs AS num_args,
t1.typname AS return_type,
a.rolname AS procedure_owner,
l.lanname AS language_type,
p.proargtypes AS argument_types_oids,
prosrc AS body
FROM pg_proc p
LEFT JOIN pg_type t1 ON p.prorettype=t1.oid
LEFT JOIN pg_authid a ON p.proowner=a.oid
LEFT JOIN pg_language l ON p.prolang=l.oid
WHERE proname = :PROCEDURE_NAME;