Re: Oracle Equivalent queries in Postgres - Mailing list pgsql-sql

Hi Pavel,
In the given link, there are no views which can give information about indexes.

Is it possible for you to give me the equivalent queries in postgres?

Sivannarayanareddy Nusum | System Analyst(Moneta GDO)

Subex Limited, Adarsh Tech Park, Outer Ring Road, Devarabisannalli, Bangalore – 560037, India.
Phone: +91 80 6696 3371; Mobile: +91 9902065831  Fax: +91 80 6696 3333;

Email:  sivannarayanreddy@subexworld.com; URL:  www.subexworld.com

 

Disclaimer: This e-mail is bound by the terms and conditions described at http://www.subexworld.com/mail-disclaimer.html

On 2/16/2011 4:20 PM, Pavel Stehule wrote:

Hello

PostgreSQL uses a different system tables than Oracle. Try to use a standardized information_schema instead - these views are same on PostgreSQL and Oracle.

http://www.postgresql.org/docs/current/static/information-schema.html

Regards

Pavel Stehule

2011/2/16 Sivannarayanreddy <sivannarayanreddy@subexworld.com>

Hello,
I am checking the compatibility of my product with Postgres database and i stucked in forming the below oracle equivalent queries in Postgres database, Could some one help me pleaseee

1) Trying to get index and corresponding columns  information of all the tables in mentioned schema

select  inx.table_name as table_name, inx.index_name as index_name,column_name, case  ( when inx.index_type = 'IOT - TOP' then 'Y' else 'N'  end,                                              
    case   
     when inx.uniqueness = 'UNIQUE' then 'Y'      
     when inx.uniqueness = 'NONUNIQUE' then 'N'    
     end,                                               
     'N' as ignore_dup_key,                            
     cast(inc.column_position as NUMBER(10))           
     from    all_indexes         inx,                          
     all_ind_columns     inc                           
      where   inx.owner           = '" + database.toUpperCase() + "'       
      and     inx.table_name      = inc.table_name              
      and     inx.index_name      = inc.index_name               
      and     inx.owner           = inc.index_owner             
     and     inx.owner           = inc.table_owner             
     and     inx.dropped         = 'NO'
     and     inx.table_name       = '" + tableName.toUpperCase() + "'
     order by inx.table_name, inx.index_name, cast(inc.column_position as NUMBER(10))


2) Trying to get the columns information of all the tables in mentioned schema

 select   tab.TABLE_NAME,
                  col.COLUMN_NAME,
                  col.DATA_TYPE,
                  cast(case  when col.CHAR_COL_DECL_LENGTH is NULL then col.DATA_PRECISION else col.CHAR_LENGTH end  as NUMBER(10)),
                  cast(col.NULLABLE as CHAR(1)),
                  cast(col.COLUMN_ID as NUMBER(10))
                 
         from    all_tab_columns    col,
                 all_tables         tab
         where   tab.TABLE_NAME        = col.TABLE_NAME
         and     tab.OWNER             = col.OWNER
         and     tab.OWNER             = '" + database.toUpperCase() + "'
         and     tab.DROPPED           = 'NO'
        and     tab.TABLE_NAME       = '" + tableName.toUpperCase() + "'
        order by tab.TABLE_NAME, cast(col.COLUMN_ID as NUMBER(10))
       

Sivannarayanareddy Nusum | System Analyst(Moneta GDO)

Subex Limited, Adarsh Tech Park, Outer Ring Road, Devarabisannalli, Bangalore – 560037, India.
Phone: +91 80 6696 3371; Mobile: +91 9902065831  Fax: +91 80 6696 3333;

Email:  sivannarayanreddy@subexworld.com; URL:  www.subexworld.com

 

Disclaimer: This e-mail is bound by the terms and conditions described at http://www.subexworld.com/mail-disclaimer.html