Re: Oracle Equivalent queries in Postgres

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(at)subexworld(dot)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(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>
>
> 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(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>
>>
>
>