Thread: Oracle Equivalent queries in Postgres
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
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; URL: www.subexworld.com
Disclaimer: This e-mail is bound by the terms and conditions described at http://www.subexworld.com/mail-disclaimer.html
Attachment
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
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; URL: www.subexworld.com
Disclaimer: This e-mail is bound by the terms and conditions described at 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 Stehule2011/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; URL: www.subexworld.com
Disclaimer: This e-mail is bound by the terms and conditions described at http://www.subexworld.com/mail-disclaimer.html
