Introduction – Implicit Conversion
Oracle to PostgreSQL migration is a playground that uncovers and touches upon many database concepts, which are always intriguing and fun to explore. Implicit Conversion, i.e., imposing automatic conversion on data types to make them comparable by database optimizers, is also a concept frequently encountered in database migrations. Implicit conversion allows for the automatic conversion of data types for an expression or condition when necessary for SQL execution, thus preventing failures.
Implicit conversion can be a performance issue in many cases, but it’s still common to encounter it in databases due to various real-world constraints. In some instances, we might utilize function-based indexes to mitigate this issue as well. In this blog, we will explore the differences in implicit conversion between Oracle and PostgreSQL and discuss methods to mitigate it. Our focus is not on comparing which database engine makes ideal choices regarding implicit conversion, but rather on achieving similar functionality in PostgreSQL when needed, such as comparing two different data types with implicit conversions.
Oracle – Implicit Conversion Observations.
Let’s begin by creating some mock tables in Oracle to understand implicit conversion characteristics. In our examples, we will focus on equality comparison between VARCHAR or TEXT datatypes and NUMBER family data types.
create table tstimplicitconv (col1 number, col2 number(6), col3 float ,
col4 varchar2(100));
insert into tstimplicitconv values (1,1,1.1,'1');
commit;
explain plan for select 1 from tstimplicitconv where col1 = col4;
select * from table(dbms_xplan.display);
explain plan for select 1 from tstimplicitconv where col4 = col2;
select * from table(dbms_xplan.display);
Tabular representation of implicit conversion behavior in Oracle for equality operator.
| Operator | Auto Conversion(YES/NO) | Implicit Conversion – Filter | |
| varchar=number | YES | filter(“COL1″=TO_NUMBER(“COL4”)) | |
| number=varchar | YES | filter(“COL2″=TO_NUMBER(“COL4”)) |
In Oracle, TEXT or VARCHAR columns undergo implicit cast operations to facilitate necessary query execution. This can potentially lead to performance concerns as indexes on columns might not be utilized, resulting in poor access patterns. One key takeaway from Oracle’s behavior is that it doesn’t fail the SQL query but imposes implicit conversion, which can lead to performance issues.
PostgreSQL – Implicit Conversion Observations.
We will create mock tables similar to those in the Oracle examples and examine how implicit casting behaves.
create table testimplicit (col1 smallint, col2 int, col3 bigint ,
col4 real , col5 double precision, col6 numeric , col7 text);
explain analyze select * from testimplicit where col1 = col7;
explain analyze select * from testimplicit where col2 = col7;
Tabular representation of implicit conversion behavior in PostgreSQL for equality operator on TEXT/VARCHAR and Numeric Family.
| Operator | Auto Conversion(YES/NO) | Additional info | |
| text=number | NO | ERROR: operator does not exist: integer = text | |
| number=text | NO | ERROR: operator does not exist: numeric = text |
In PostgreSQL TEXT or VARCHAR columns does not underdo implicit cast operations to facilitate necessary query execution instead it fails as euality operator does not support comparision.
The default equality operator supported by PostgreSQL compares arguments within the same data type family. Below is some of the primitive argument types and Equality (=) operator available in PostgreSQL.
Some Workarounds
Due to differences in implicit conversions between both engines, it’s common for customers to encounter issues where their code, previously functional in Oracle, begins to fail with operator exceptions in PostgreSQL. The underlying reason often lies in how Oracle handles and conceals implicit conversions, which may not be apparent until migration. In most of the cases migrations also present opportunities to modernize databases whenever feasible.
Mitigation option 1 :- Add Explicit casting if necessary.
We can explicitly add necessary casting operators whenever argument types within equality operator is not from same family types.
However, manually fixing all such occurrences during migrations can be cumbersome and time-consuming. Issues may arise, especially with SQL embedded within application code logic, making it a tedious task to address across databases.
Mitigation option 2 :- Add Custom Operators.
PostgreSQL allows us to create new operators within user defined schema that we can imposed as required in comparision or in our case for equality comparision. We can use new operator for different comparision between TEXT/VARCHAR and NUMERIC family argument types.
We will have to create an user defined functions that will impose necessary casting and an equality operator to support argument types of TEXT and NUMERIC. Below is one such sample for Integer and text argument types.
CREATE SCHEMA ora_operators;
CREATE FUNCTION ora_operators.equal(integer, text)
RETURNS boolean
LANGUAGE sql IMMUTABLE STRICT
AS $CAST$SELECT $1 = CAST($2 AS INTEGER);$CAST$;
CREATE FUNCTION ora_operators.equal(text, integer)
RETURNS boolean
LANGUAGE sql IMMUTABLE STRICT
AS $CAST$SELECT $2 = CAST($1 AS INTEGER);$CAST$;
CREATE OPERATOR ora_operators.= (
FUNCTION = ora_operators.equal,
LEFTARG = text,
RIGHTARG = integer,
COMMUTATOR = OPERATOR(ora_operators.=),
NEGATOR = OPERATOR(ora_operators.<>)
);
CREATE OPERATOR ora_operators.= (
FUNCTION = ora_operators.equal,
LEFTARG = integer,
RIGHTARG = text,
COMMUTATOR = OPERATOR(ora_operators.=),
NEGATOR = OPERATOR(ora_operators.<>)
);
All default operators provided by PostgreSQL are well-defined in the pg_catalog schema. If we need to use our explicitly defined operators in a custom schema, we will need to specify them explicitly.
explain (analyze,buffers) select * from testimplicit
where col2 OPERATOR(ora_operators.=) col7;
Now, the SQL execution didn’t fail, and it employs our custom function to handle the required casting, as indicated in the execution plan. However, the workaround isn’t complete yet, as adding a custom operator for every occurrence of implicit casting will still be a cumbersome approach.”
In PostgreSQL, by default, anything defined in pg_catalog takes precedence over what is defined in a user-defined schema. This includes user-defined operators. We want our equality operator (=) to be prioritized first, followed by any default operators. To achieve this, we will customize the search_path by explicitly placing our custom schema before the pg_catalog schema.
blog=# set search_path=ora_operators,pg_catalog,"$user",public;
SET
blog=# show search_path;
search_path
--------------------------------------------
ora_operators, pg_catalog, "$user", public
(1 row)
select * from testimplicit
where col2=col7;
Summary
Implicit casting is a common migration challenge encountered in Oracle to PostgreSQL migrations. By default, PostgreSQL does not support implicit casting for comparison between TEXT and NUMBER family types, which may lead to exceptions and failed SQL queries that were running successfully in Oracle. One workaround to fix this issue is explicit casting, but applying this across all occurrences in the databases may not be feasible. Alternatively, creating custom operators and defining our own casting functions in PostgreSQL can provide a solution, allowing for implicit casting when needed.
Repo link on custom equality operator for all numeric family types with text.
Please note*
I am not 100% sure if we have any implications of changing search_path that precedes custom schema over pg_catalog. Please verify it further before using it.
If you really enjoyed the blog and are exploring courses on Oracle to PostgreSQL Migration, please check out my Live Courses!
Database and Migration Insights 