Sql:postgresql SQL Script

SELECT * from Pg_class c,pg_attribute a,pg_type t where c.relname= ' bookkindlist ' and a.attnum>0 and a.attrelid=c.oid an D a.atttypid=t.oidselect a.attname from Pg_class c,pg_attribute a,pg_type t where c.relname= ' bookkindlist ' and A.ATTNUM&G T;0 and A.attrelid=c.oid and a.atttypid=t.oid--querying field information for bookkindlist tables 20150402 geovin Du-Tu-a.attnum,a.attname Eld,t.typname as type,a.attlen as length,a.atttypmod as lengthvar,a.attnotnull as Notnull from Pg_class C,pg_attribute a,p G_type t where c.relname= ' bookkindlist ' and a.attnum>0 and A.attrelid=c.oid and A.atttypid=t.oidselect * from Informati On_schema.columns; --List of check lists 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 ';--View Database select * from pg_database;--view table space Select * from Pg_tablespace; --View Language select * from Pg_language; --ViewRole User SELECT * from Pg_user;select * from Pg_shadow;select * from Pg_roles; --View Session Process select * from pg_stat_activity; --View Table select * from pg_tables where schemaname = ' public '; --View table field select * from information_schema.columns where table_schema = ' public ' and table_name = ' bookkindlist '; --View View select * from pg_views where schemaname = "public"; select * from information_schema.views where table_schema = ' Publ IC '; --View Trigger select * from Information_schema.triggers;  --View Sequence select * from information_schema.sequences where Sequence_schema = ' public ';  --View Constraints 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 '; --View Index SELECT * from Pg_index; --See which indexes exist on the table and the size of 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_kbfrom pg_class C, Pg_class C2, pg_index iwhere c.relname = ' Bookkindli  St ' Andc.oid = i.indrelid andc2.oid = I.indexrelidorder by c2.relname; --View index definition Select B.indexrelid from pg_class a,pg_index b where a.oid = b.indrelid and a.relname = ' bookkindlist '; Select Pg_g Et_indexdef (B.indexrelid); --View procedure function definition Select oid,* from pg_proc where proname = ' insert_platform_action_exist '; --oid = 24610select * from Pg_get_functiondef (24610);                         --View the table size (without information such as indexes) Select Pg_relation_size (' bookkindlist '); --368640 byteselect Pg_size_pretty (pg_relation_size (' bookkindlist '))--360 KB--View DB size Select Pg_size_pretty (pg_   Database_size (' Geovindu ')); --12M--View server DB run status [[email protected] ~]$ pg_ctl status-d $PGDATApg _ctl:server is running (pid:2373)/home/postgre S/bin/postgres "-D" "/database/pgdata"--View the usage of each db (read, write, cache, update, transaction, etc.) SELECT * FROM pg_sTat_database--View the usage of the index select * from Pg_stat_user_indexes; --View the data file path and size of the table for select Pg_relation_filepath (OID), relpages from pg_class WHERE relname = ' bookkindlist '; --View index and related fields and sizes 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_attrib Ute A,--Att in base pg_roles r WHERE bc.relnamespace = n.oid and I.indrelid = Bc.oid and I.inde Xrelid = 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, Attnam E --View PG Lock SELECT * from Pg_locks; Note: relpages*8 is the actual disk size--View table space size Select Pg_tablespace_size (' Pg_defaUlt '); --View the correspondence between the sequence and the table with Fq_objects as (SELECT c.oid,c.relname as Fqname, C.relkind, c.relname as re Lation from Pg_class C joins pg_namespace n on n.oid = c.relnamespace), sequences as (SELECT oid,f          QName from fq_objects where relkind = ' S '), tables as (SELECT oid, fqname from fq_objects WHERE relkind = ' R ') SELECT s.fqname as sequence, '-I ' as depends, t.fqname as table from Pg_depend D JO In 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; ---A query table structure for 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.descriptionfrom information_schema.columns Col left JOIN Pg_descripti On des Col.table_name::regclass = des.objoid and col.ordinal_position = des.objsubidwhere Table_schema = ' Public ' and table_name = ' bookkindlist ' ORDER by Ordinal_position;select * from Pg_namespaceselect * from Pg_class WH ere relname= ' bookkindlist '---selectn.nspname, relnamefrompg_class c, pg_namespace nwherec.relnamespace = N.oidAND Nspname= ' public ' and relkind = ' R ' and Relhassubclassorder Bynspname, Relname;--select * from Information_schema.columns WHERE table_name = ' bookkindlist ';--table structure 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 ';--table primary Key name Select Pg_constraint.conname as Pk_name from Pg_constraint inner join Pg_class on Pg_con Straint.conrelid = pg_class.oid where pg_class.relname = ' bookkindlist ' and pg_constraint.contype= ' P ';--Table primary key field 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.attnu m = Pg_constraint.conkey[1]inner Join Pg_type on pg_type.oid = Pg_attribute.atttypidinner Join Information_ Schema.columns as info on info.column_name=pg_attribute.attname where pg_class.relname = ' bookkindlist ' and pg_ Constraint.contype= ' P ';--table primary Key name Select Conname,conrelid,connamespace from Pg_constraint where contype= ' p '; select * FROM Pg_constraint where contype= ' P ';---table and table primary key name Select Oid,relname from Pg_class;selecT * from Pg_class where Relnamespace=2200;select * from pg_class;--table select * from Pg_type where Typnamespace=2200;select t Ypnamespace,typname,oid from Pg_type where Typnamespace=2200;select * from Pg_type where typname= ' bookkindlist ';-- Primary key field name Attnameselect * FROM Pg_attribute;select * from Pg_attribute where attstorage= ' P '; Select Attrelid,attname,attnum From Pg_attribute where attstorage= ' P ';

　　

Sql:postgresql SQL Script