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.

Null statements

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 above)

Because a null chain text is not a NULL value... So here, '' is returned instead of 'N/A' !

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.

Numbers

ora.round(numeric,integer)

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 ::type operator:

select round('2.0034'::numeric,1);
 round
-------
   2.0
(1 row)

select round('2.0034'::numeric,1)::integer;
 round
-------
     2
(1 row)

ora.round(interval,integer)

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 extract():

select extract('year' from current_timestamp);

 extract
---------
    2022
(1 row)

This extraction will give the exact year (here in the examples, works with Day or Month too..). But WON'T round to the nearest year as per Orafce doc example.

To achieve this, and if you really need it in your business case, we have to compare 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.

ora.to_number replacement

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)

The third oracle.to_number(numeric,numeric) : we assume it's about precision, since not documented much in Orafce... This can be achieved with ROUNDING:

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)

Dates

ora.sysdate() replacement

Seen in logs:

ora.SYSDATE()

This is trivial again with PostgreSQL, it's called current_date, and if you need more precision, 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)

Converting current_date (or any date) to a timestamp will add 00:00:00 hour to it:

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 LOCALTIME and/or LOCALTIMESTAMP:

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 ora.SYSDATE() with localtimestamp(0).

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)

ora.to_date() replacement

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.

ora.trunc replacement

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)

More info:

• PostgreSQL doc on date/time functions
• Template Patterns for Date/Time Formatting

ora.add_months replacement

Seen in logs:

ora.trunc(ora.add_months(Dr.Event_Date,1),'MONTH')

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)

Final thoughts

• 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.