In my project, I originally used an Oracle database but later switched to PostgreSQL. To avoid rewriting SQL, I used the orafce extension to keep Oracle-specific functions working.

Now, I'm switching to native PostgreSQL functions to remove the orafce extension. However, I ran into a problem:

ERROR: operator does not exist: bigint = character varying

that comes from:

select 1 from my_table where my_table_id = '100';

which works in oracle but not pgs

my question is: does orafce ensure Implicit Data Conversion like oracle and once i removed it it won't work anymore

In my project, I originally used an Oracle database but later switched to PostgreSQL. To avoid rewriting SQL, I used the orafce extension to keep Oracle-specific functions working.

Now, I'm switching to native PostgreSQL functions to remove the orafce extension. However, I ran into a problem:

ERROR: operator does not exist: bigint = character varying

that comes from:

select 1 from my_table where my_table_id = '100';

which works in oracle but not pgs

my question is: does orafce ensure Implicit Data Conversion like oracle and once i removed it it won't work anymore

• postgresql
• oracle-database
• orafce

• 1) What version of Postgres? 2) select 100::bigint = '100'; t and select 100::bigint = '100'::unknown; t. So in what client is the query being run? – Adrian Klaver Commented Mar 20 at 15:54

1 Answer 1

I am a author Orafce, and yes, Orafce supports lot of implicit casts, that are unsupported by Postgres without Orafce (or requires explicit casting). Generally, Oracle type system is very different than PostgreSQL type system. The base is very different. At the end, the Oracle is much more type tolerant, and Postgres is type strict system. Generally Postgres type system is working pretty well, but requires more clean style. Oracle allows very dirty (almost ugly) style of coding.