Oracle to Postgres Post-Migration Improvements
With Crunchy Data's focus on Postgres we often talk with people that are looking to reduce their dependency on other databases and modernize their data stack. A big portion of that is migrating and building new apps on Postgres. One common tool in migration from an Oracle database is Orafce which imitates many Oracle functions inside PostgreSQL. Using a tool like Orafce can assist greatly with the migration process. Long term, however, it is likely good practice to slowly refactor your application code to talk directly to Postgres.
Every statement calling
ora.function_name() will add overhead in calculations.
This overhead is typically pretty low for any given call. However, the amount of
queries that use it on a very busy database can see some performance
improvements for removing these functions.
The following post is a collection of notes on how to replace Orafce functions with Postgres functions. To find the most commonly used functions, you can refer to your logs and just replace the ones you see used most often.
Here's a few examples of things we found and how to update them.
ora.nvl(MY_FIELD1,'N/A') , ora.nvl(character varying,character varying) ora.nvl(numeric,numeric) ora.nvl(timestamp without time zone,timestamp without time zone)
nvl() in Oracle returns 1st non null value in a list.
The replacement in PostgreSQL is trivial, just use coalesce() instead:
select coalesce(NULL,1,2); coalesce ---------- 1 (1 row) select coalesce(NULL,'N/A'); coalesce ---------- N/A (1 row)
BEWARE of this:
select coalesce('','N/A'); coalesce ---------- (1 row)
This means that
'' is not equal to
NULL in PostgreSQL! (see last URL/PDF
Because a null chain text is not a
NULL value... So here,
'' is returned
More info on: PostgreSQL tutorial article and the PostgreSQL documentation.
Special Warning : NULL in PostgreSQL is a special value... Can be disturbing for Oracle devs and dbas. A must read article from Bruce Momjian is a great place to start.
This kind of thing is trivial to replace with PostgreSQL:
select round('2.0034',1); round ------- 2.0 (1 row)
In this example, numeric datatype is assumed by PostgreSQL.
Explicit conversion can be achieved anywhere in PostgreSQL thanks to the special
select round('2.0034'::numeric,1); round ------- 2.0 (1 row) select round('2.0034'::numeric,1)::integer; round ------- 2 (1 row)
per Orafce doc:
round(date, text) date - will round dates according to the specified format ound(date '2005-07-12', 'yyyy') -> 2006-01-01
If you need in your app only to "extract a year from a date", REPLACE with
select extract('year' from current_timestamp); extract --------- 2022 (1 row)
This extraction will give the exact year (here in the examples, works with
Month too..). But WON'T round to the nearest year as per Orafce doc
To achieve this, and if you really need it in your business case, we have to
age() of the date with start of year and end of year. Smaller age
means "closer to the next year", so this is working:
select case when age('2022-12-31'::date, current_timestamp) > age(current_timestamp,'2022-01-01'::date) then extract('year' from current_timestamp) else extract('year' from current_timestamp)+1 end as closest_year; closest_year -------------- 2023 (1 row)
Beware of the order of dates in the
age()>age(), if you don't order it like
this, it may result in NEGATIVE age, so the
> won't work the way you expect,
since a negative age is always compared to a positive one.
Seen in logs:
ora.to_number(interval) ora.to_number(character varying) ora.to_number(numeric)
Per Orafce documentation::
oracle.to_number(text) - converts a string to a number oracle.to_number(numeric) - converts a string to a number oracle.to_number(numeric,numeric) - converts a string to a number
First two are trivial: use
::type with correct types, if you don't mind much,
numeric will do the trick for any number:
select '12'::smallint; int2 ------ 12 (1 row) select '1202348'::smallint; ERROR: value "1202348" is out of range for type smallint select '1202348'::numeric; numeric --------- 1202348 (1 row)
oracle.to_number(numeric,numeric) : we assume it's about precision,
since not documented much in Orafce... This can be achieved with
select round('1202348.045'::numeric,2); round ------------ 1202348.05 (1 row)
If you don't want rounding, then you can also
TRUNCATE for different behavior:
select trunc('1202348.045'::numeric,2); trunc ------------ 1202348.04 (1 row)
Seen in logs:
This is trivial again with PostgreSQL, it's called current_date, and if you need
current_timestamp (time in the time zone of the server):
select current_date; current_date -------------- 2022-09-15 (1 row) select current_timestamp; current_timestamp ----------------------------- 2022-09-15 11:32:27.6182+02 (1 row)
current_date (or any date) to a timestamp will add 00:00:00 hour to
select current_date::timestamp; current_date --------------------- 2022-09-15 00:00:00 (1 row)
Per Orafce documentation:
oracle.sysdate() - Returns statement timestamp at server timezone (orafce.timezone) oracle.sysdate() -> 2015-12-09 17:47:56
As you read before there's this
+2 on the timestamp: it shows the timezone.
It's always a best practice to store timezone, but if you want perfect match
with what gives Orafce, then we have to use
select localtime; localtime ----------------- 11:37:58.731723 (1 row)
Gives just the local time of the server, without timezone.
select localtimestamp; localtimestamp ---------------------------- 2022-09-15 11:38:19.591779 (1 row)
Now, to be perfect match, we need to tell PostgreSQL we don't need precision at all:
select localtimestamp(0); localtimestamp --------------------- 2022-09-15 11:38:40 (1 row)
In short, in your app, replace
Just a cool trick added: computing like timestamp + interval is easy with PostgreSQL: you can add (or remove) some interval to a timestamp or date with:
select current_timestamp(0)+'1 day'::interval as tomorrow_same_hour; tomorrow_same_hour ------------------------ 2022-09-16 11:39:24+02 (1 row) select current_timestamp(0)-'1 year'::interval as one_year_ago; one_year_ago ------------------------ 2021-09-15 11:39:40+02 (1 row) select current_timestamp(0)-'1 year 3 days 15 minutes'::interval as one_year_ago_and_less; one_year_ago_and_less ------------------------ 2021-09-12 11:25:01+02 (1 row)
Seen in logs:
ora.to_date('20220312','YYYYMMDD') ora.to_date(character varying,character varying,character varying) ora.to_date(days_keep_dm_transaction, 'YYYYMMDD')
This is trivial to replace with PostgreSQL : same function and parameters:
select to_date('20220312','YYYYMMDD'); to_date ------------ 2022-03-12 (1 row)
No need to call Orafce here.
Seen in logs:
ora.trunc(Event_Date,'DD') ora.trunc(Event_Date,'HH') ora.trunc(Event_Date,'MONTH') ora.trunc(Event_Date + ora.numtodsinterval(1/24,'Days'),'HH') ora.trunc(Event_Date + ora.numtodsinterval(1,'Days'),'DD')
Per Orafce documentation::
trunc(date, text) date - truncate date according to the specified format trunc(date '2005-07-12', 'iw') -> 2005-07-11
The corresponding function in PostgreSQL here is
date_trunc. When dealing with
dates, it accepts as a parameter a Template Pattern for Date/Time (see link
above) then a timestamp, and returns a timestamp.
So first, beware to modify the order of parameters, it's reverse here.
And second, choose wisely the template pattern you need.
To match the Orafce documentation example, that returns a date, just cast the date given in parameter as a timestamp, and also cast the result of the function:
select date_trunc('week','2005-07-12'::timestamp)::date; date_trunc -------------- 2005-07-11 (1 row)
Seen in logs:
Simply add an interval of given months to your date or timestamp:
select localtimestamp+'1 month'::interval; ?column? ---------------------------- 2022-10-15 11:43:12.417268 (1 row)
- Crunchy Data supports customers migrating. We don't stop there, we'll work with you on the long term performance improvements for the life of your database.
- If you've used Orafce to migrate to Postgres, think about replacing queries one at a time in your application code.
- Start with your logs and find the most used queries. That's where this work will have the biggest impact.
Looking for help migrating to Postgres? Reach out.
October 5, 2022 •More by this author