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.

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')

Execute these queries in your database and get all result.