Extracting Information Schema Using Postgres Sql

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.

Leave a Reply

Your email address will not be published. Required fields are marked *

Time limit is exhausted. Please reload the CAPTCHA.



nike tn pas cher tn pas cher peuterey outlet piumini peuterey outlet spaccio peuterey outlet peuterey outlet piumini peuterey outlet spaccio peuterey outlet hogan outlet scarpe hogan outlet hogan outlet online peuterey outlet piumini peuterey outlet spaccio peuterey outlet hogan outlet scarpe hogan outlet hogan outlet online hogan outlet scarpe hogan outlet hogan outlet online doudoune moncler pas cher moncler pas cher doudoune moncler hogan outlet scarpe hogan outlet hogan outlet online nike tn pas cher tn pas cher