In an earlier post we looked at the Queries for Sql Server for retrieving meta information. In this post, we will look at the PostgresSql version.

Get All Views

 SELECT table_name,        view_definition FROM   information_schema.VIEWS WHERE  table_schema = ANY ( Current_schemas(FALSE) );

Get All Tables

 SELECT table_name FROM   information_schema.TABLES WHERE  table_schema = ANY ( Current_schemas(FALSE) )

Get All Columns

 SELECT column_name,        is_nullable,        is_identity,        character_maximum_length,        numeric_precision,        numeric_precision_radix,        numeric_scale,        data_type FROM   information_schema.COLUMNS WHERE  table_schema = 'public'        AND table_name = '<TableName>'

Get All Indices from database

 SELECT ix.indisunique AS IsUnique,        t.relname      AS TableName,        i.relname      AS Name,        a.attname      AS ColumnName FROM   pg_class t,        pg_class i,        pg_index ix,        pg_attribute a WHERE  t.oid = ix.indrelid        AND i.oid = ix.indexrelid        AND a.attrelid = t.oid        AND a.attnum = ANY ( ix.indkey )        AND t.relkind = 'r'        AND ix.indisprimary = FALSE        AND t.relname = '<TableName>';

Get All Primary Key Constraints

 SELECT kcu.table_schema,        tco.constraint_name,        kcu.column_name FROM   information_schema.table_constraints tco        join information_schema.key_column_usage kcu          ON kcu.constraint_name = tco.constraint_name             AND kcu.constraint_schema = tco.constraint_schema             AND kcu.constraint_name = tco.constraint_name WHERE  tco.constraint_type = 'PRIMARY KEY'        AND kcu.table_name = '<TableName>' ORDER  BY kcu.table_schema,           kcu.table_name

Get all Unique Key Constraints

 SELECT kcu.table_schema,        tco.constraint_name,        kcu.column_name FROM   information_schema.table_constraints tco        join information_schema.key_column_usage kcu          ON kcu.constraint_name = tco.constraint_name             AND kcu.constraint_schema = tco.constraint_schema             AND kcu.constraint_name = tco.constraint_name WHERE  tco.constraint_type = 'UNIQUE'        AND kcu.table_name = '<TableName>' ORDER  BY kcu.table_schema,           kcu.table_name;

Get all check constraints

  SELECT col.column_name    AS ColumnName,        tc.constraint_name AS Name,        cc.check_clause    AS DEFINITION FROM   information_schema.table_constraints tc        join information_schema.check_constraints cc          ON tc.constraint_schema = cc.constraint_schema             AND tc.constraint_name = cc.constraint_name        join pg_namespace nsp          ON nsp.nspname = cc.constraint_schema        join pg_constraint pgc          ON pgc.conname = cc.constraint_name             AND pgc.connamespace = nsp.oid             AND pgc.contype = 'c'        join information_schema.COLUMNS col          ON col.table_schema = tc.table_schema             AND col.table_name = tc.table_name             AND col.ordinal_position = ANY ( pgc.conkey ) WHERE  tc.constraint_schema NOT IN( 'pg_catalog', 'information_schema' ) ORDER  BY tc.table_schema,           tc.table_name;

Get all Foreign Key Constraints 

  SELECT tc.constraint_name,        kcu.column_name,        ccu.table_name,        ccu.column_name FROM   information_schema.table_constraints AS tc        join information_schema.key_column_usage AS kcu          ON tc.constraint_name = kcu.constraint_name             AND tc.table_schema = kcu.table_schema        join information_schema.constraint_column_usage AS ccu          ON ccu.constraint_name = tc.constraint_name             AND ccu.table_schema = tc.table_schema WHERE  tc.constraint_type = 'FOREIGN KEY'        AND tc.table_name = '<TableName>'

Get all Default value Constraints

  SELECT col.column_name    AS ColumnName,        col.column_default AS DefaultValue FROM   information_schema.COLUMNS col WHERE  col.column_default IS NOT NULL        AND col.table_schema NOT IN( 'information_schema', 'pg_catalog' )        AND col.table_name = '<TableName>' ORDER  BY col.column_name;