From: Sivannarayanreddy <sivannarayanreddy@subexworld.com>
To: pgsql-sql@postgresql.org
Subject: Oracle Equivalent queries in Postgres
Date: Wed, 16 Feb 2011 16:12:15 +0530
Message-ID: <4D5BAA07.7060704@subexworld.com> (raw)
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
<mailto:email.id@subexworld.com>; *URL:*www.subexworld.com
<http://www.subexworld.com/;
/Disclaimer: This e-mail is bound by the terms and conditions described
at //http://www.subexworld.com/mail-disclaimer.html/Attachments:
[image/gif] graphics1 (5.5K, ../4D5BAA07.7060704@subexworld.com/3-graphics1)
download | view image
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: sivannarayanreddy@subexworld.com
Subject: Re: Oracle Equivalent queries in Postgres
In-Reply-To: <4D5BAA07.7060704@subexworld.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox