Cheat Code for Database Meta information : PostgreSql

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;  

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s