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 <sivannarayanre...@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:* sivannarayanre...@subexworld.com <email...@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> >
<<graphics1>>
