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