Common SQL statements in PostgreSQL

-- View Database
Select * From pg_database;

-- View tablespaces
Select * From pg_tablespace;

-- View Language
Select * From pg_language;

-- View Role users
Select * From pg_user;
Select * From pg_shadow;
Select * From pg_roles;

-- View the session Process
Select * From pg_stat_activity;

-- View table
Select * From pg_tables where schemaname = 'public ';

-- View table fields
Select * From information_schema.columns where table_schema = 'public' and table_name = 'pf _ vip_org ';

-- View
Select * From pg_views where schemaname = 'public ';
Select * From information_schema.views where table_schema = 'public ';

-- View triggers
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'

Select * From pg_constraint where connamespace = 2200 and conrelid = 24826
-- U unique, P primary, f Foreign, C check, t trigger, X exclusion

Select. relname as table_name, B. conname as constraint_name, B. contype as constraint_type from pg_class A, pg_constraint B where. oid = B. conrelid and. relname = 'cc ';

-- View process functions (including trigger functions and functions)
Select OID, * From pg_proc where pronamespaces = 2200;

-- OID 2200 is public

 

-- View Indexes
Select * From pg_index;

-- View the indexes and sizes on the table.
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;

-- View index Definitions
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 );

-- View process function definitions
Select OID, * From pg_proc where proname = 'insert _ platform_action_exist '; -- OID = 24610
Select * From pg_get_functiondef (24610 );

-- View the table size (excluding index and other information)
Select pg_relation_size ('cc'); -- 368640 byte
Select pg_size_pretty (pg_relation_size ('cc') -- 360 KB

-- View the database size
Select pg_size_pretty (pg_database_size ('smiletao'); -- 12 m

-- View the running status of the server database
[S @ eyar ~] $ Pg_ctl status-d $ pgdata
Pg_ctl: server is running (PID: 2373)
/Home/Postgres/bin/Postgres "-d" "/database/pgdata"

-- View the usage of each database (read, write, cache, update, transaction, etc)
Select * From pg_stat_database

-- View index usage
Select * From pg_stat_user_indexes;

-- View the data file path and size corresponding to the table
Select pg_relation_filepath (OID), relpages from pg_class where relname = 'empsalary ';

-- View indexes 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_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;

-- View PG locks
Select * From pg_locks;

Note: relpages * 8 indicates the actual disk size.

-- View the tablespace size
Select pg_tablespace_size ('pg _ default ');

-- View the correspondence between sequences and tables
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 ';

-- Database paging query ---

Select ctid, * From kakou_clxx limit 10 offset 10
Select ctid, * From kakou_clxx limit 10 offset 20