This Tutorial introduces how to extract Information Schema in PostgreSQL with simple Sql queries. Information schema provides information about tables, columns, views, trigger, functions and sequences in a database. We extract these information from simple SQL queries, lets we have a well maintained database and below are simple Sql statements to get various information in our database.
[sql]
List All Users
SELECT usename
FROM pg_user;
List All Tables Name
SELECT table_name
FROM information_schema.tables
WHERE table_type = ‘BASE TABLE’
AND table_schema NOT IN
(‘pg_catalog’, ‘information_schema’);
List All Views
SELECT table_name
FROM information_schema.views
WHERE table_schema NOT IN (‘pg_catalog’, ‘information_schema’)
AND table_name !~ ‘^pg_’;
List All Table Name
SELECT table_name FROM information_schema.tables
WHERE table_type = ‘BASE TABLE’ AND table_schema NOT IN
(‘pg_catalog’, ‘information_schema’)
List Name of the field, data type of table.
// Suppose emp is table name
SELECT column_name, data_type
FROM information_schema.columns
WHERE table_name = ’emp’;
List Table Indexes
// Suppose emp is table name
SELECT relname FROM pg_class
WHERE oid IN
(SELECT indexrelid FROM pg_index, pg_class
WHERE pg_class.relname=’emp’
AND pg_class.oid=pg_index.indrelid
AND indisunique != ‘t’ AND indisprimary != ‘t’)
List Table Constraints
SELECT constraint_name, constraint_type FROM information_schema.table_constraints
WHERE table_name = ’emp’ AND constraint_type!=’CHECK’
List Triggers
SELECT DISTINCT trigger_name FROM information_schema.triggers
WHERE event_object_table = ’emp’
AND trigger_schema NOT IN (‘pg_catalog’, ‘information_schema’)
List Functions
SELECT routine_name FROM information_schema.routines
WHERE specific_schema NOT IN (‘pg_catalog’, ‘information_schema’)
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’)
[/sql]
Execute these queries in your database and get all result.