pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedOracle Equivalent queries in Postgres
8+ messages / 5 participants
[nested] [flat]
* Oracle Equivalent queries in Postgres
@ 2011-02-16 10:42 Sivannarayanreddy <sivannarayanreddy@subexworld.com>
0 siblings, 2 replies; 8+ messages in thread
From: Sivannarayanreddy @ 2011-02-16 10:42 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Oracle Equivalent queries in Postgres
@ 2011-02-16 10:50 Pavel Stehule <pavel.stehule@gmail.com>
parent: Sivannarayanreddy <sivannarayanreddy@subexworld.com>
1 sibling, 2 replies; 8+ messages in thread
From: Pavel Stehule @ 2011-02-16 10:50 UTC (permalink / raw)
To: sivannarayanreddy@subexworld.com; +Cc: pgsql-sql
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 <email.id@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*<http://www.subexworld.com/mail-disclaimer.html;
>
Attachments:
[image/gif] graphics1 (5.5K, ../../AANLkTinJ-0jttRsp_DysUT+a8HAv-sXxK4xy5aCmM60H@mail.gmail.com/3-graphics1)
download | view image
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Oracle Equivalent queries in Postgres
@ 2011-02-16 11:04 Thomas Kellerer <spam_eater@gmx.net>
parent: Pavel Stehule <pavel.stehule@gmail.com>
1 sibling, 1 reply; 8+ messages in thread
From: Thomas Kellerer @ 2011-02-16 11:04 UTC (permalink / raw)
To: pgsql-sql
Pavel Stehule, 16.02.2011 11:50:
> Try to use a standardized information_schema instead - these views are same on PostgreSQL and Oracle.
Unfortunately they are not the same: Oracle does not support INFORMATION_SCHEMA
Regards
Thomas
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Oracle Equivalent queries in Postgres
@ 2011-02-16 11:17 Pavel Stehule <pavel.stehule@gmail.com>
parent: Pavel Stehule <pavel.stehule@gmail.com>
1 sibling, 0 replies; 8+ messages in thread
From: Pavel Stehule @ 2011-02-16 11:17 UTC (permalink / raw)
To: sivannarayanreddy@subexworld.com; +Cc: pgsql-sql
hello
list of tables
http://www.postgresql.org/docs/current/static/infoschema-tables.html
information about column
http://www.postgresql.org/docs/current/static/infoschema-columns.html
information about indexes - it's not part of ANSI/SQL so you have to look to
pg_index or pg_indexes.
Regards
Pavel Stehule
2011/2/16 Sivannarayanreddy <sivannarayanreddy@subexworld.com>
> 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 <email.id@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*<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 <email.id@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*<http://www.subexworld.com/mail-disclaimer.html;
>>
>
>
Attachments:
[image/gif] graphics1 (5.5K, ../../AANLkTinf5h7XOGMGw2dYY9E5t8Knr0mz5X8tcST=kZGr@mail.gmail.com/3-graphics1)
download | view image
[image/gif] graphics1 (5.5K, ../../AANLkTinf5h7XOGMGw2dYY9E5t8Knr0mz5X8tcST=kZGr@mail.gmail.com/4-graphics1)
download | view image
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Oracle Equivalent queries in Postgres
@ 2011-02-16 11:20 Pavel Stehule <pavel.stehule@gmail.com>
parent: Thomas Kellerer <spam_eater@gmx.net>
0 siblings, 1 reply; 8+ messages in thread
From: Pavel Stehule @ 2011-02-16 11:20 UTC (permalink / raw)
To: Thomas Kellerer <spam_eater@gmx.net>; +Cc: pgsql-sql
2011/2/16 Thomas Kellerer <spam_eater@gmx.net>:
> Pavel Stehule, 16.02.2011 11:50:
>>
>> Try to use a standardized information_schema instead - these views are
>> same on PostgreSQL and Oracle.
>
> Unfortunately they are not the same: Oracle does not support
> INFORMATION_SCHEMA
>
sorry, I expected so all mature databases support it.
Regards
Pavel
> Regards
> Thomas
>
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Oracle Equivalent queries in Postgres
@ 2011-02-16 11:33 Thomas Kellerer <spam_eater@gmx.net>
parent: Pavel Stehule <pavel.stehule@gmail.com>
0 siblings, 1 reply; 8+ messages in thread
From: Thomas Kellerer @ 2011-02-16 11:33 UTC (permalink / raw)
To: pgsql-sql
Pavel Stehule, 16.02.2011 12:20:
>> Unfortunately they are not the same: Oracle does not support
>> INFORMATION_SCHEMA
>>
>
> sorry, I expected so all mature databases support it.
Yes, this is really hard to understand.
I would assume creating the INFORMATION_SCHEMA views based on the existing Oracle views is just a matter of maybe 3-4 days of days work. So it is really not understandable that Oracle does not support this.
But then they probably don't care - after all it's Oracle.
Regards
Thomas
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Oracle Equivalent queries in Postgres
@ 2011-02-16 15:11 Tom Lane <tgl@sss.pgh.pa.us>
parent: Thomas Kellerer <spam_eater@gmx.net>
0 siblings, 0 replies; 8+ messages in thread
From: Tom Lane @ 2011-02-16 15:11 UTC (permalink / raw)
To: Thomas Kellerer <spam_eater@gmx.net>; +Cc: pgsql-sql
Thomas Kellerer <spam_eater@gmx.net> writes:
> Pavel Stehule, 16.02.2011 12:20:
>>> Unfortunately they are not the same: Oracle does not support
>>> INFORMATION_SCHEMA
>> sorry, I expected so all mature databases support it.
> Yes, this is really hard to understand.
> I would assume creating the INFORMATION_SCHEMA views based on the existing Oracle views is just a matter of maybe 3-4 days of days work. So it is really not understandable that Oracle does not support this.
> But then they probably don't care - after all it's Oracle.
No, from their point of view it would be actively damaging: providing
standardized views would reduce customer lock-in, by making applications
more portable to other DBMSes. The pain the OP is feeling is a
marketing advantage, so far as Oracle is concerned.
regards, tom lane
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Oracle Equivalent queries in Postgres
@ 2011-02-16 17:28 bricklen <bricklen@gmail.com>
parent: Sivannarayanreddy <sivannarayanreddy@subexworld.com>
1 sibling, 0 replies; 8+ messages in thread
From: bricklen @ 2011-02-16 17:28 UTC (permalink / raw)
To: sivannarayanreddy@subexworld.com; +Cc: pgsql-sql
On Wed, Feb 16, 2011 at 2:42 AM, Sivannarayanreddy <
sivannarayanreddy@subexworld.com> wrote:
> 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
>
>
>
There is a sourceforge project that attempts to port the information_schema
to Oracle, though I haven't tried it.
http://sourceforge.net/projects/ora-info-schema/
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2011-02-16 17:28 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2011-02-16 10:42 Oracle Equivalent queries in Postgres Sivannarayanreddy <sivannarayanreddy@subexworld.com>
2011-02-16 10:50 ` Pavel Stehule <pavel.stehule@gmail.com>
2011-02-16 11:04 ` Thomas Kellerer <spam_eater@gmx.net>
2011-02-16 11:20 ` Pavel Stehule <pavel.stehule@gmail.com>
2011-02-16 11:33 ` Thomas Kellerer <spam_eater@gmx.net>
2011-02-16 15:11 ` Tom Lane <tgl@sss.pgh.pa.us>
2011-02-16 11:17 ` Pavel Stehule <pavel.stehule@gmail.com>
2011-02-16 17:28 ` bricklen <bricklen@gmail.com>
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