Re: Oracle Equivalent queries in Postgres

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(at)subexworld(dot)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(at)subexworld(dot)com <email(dot)id(at)subexworld(dot)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*<http://www.subexworld.com/mail-disclaimer.html>
>