Few tips to help migrating from Oracle to PostgreSQL

Oracle database was default choice few (tens) years ago. Now it’s an overpriced piece of software that almost nobody needs (or use to its full potential). A lot of companies are migrating from Oracle to PostgreSQL.

I did a few of these projects quite successfully so I decided to write some tips for what worked for us.

Our stack usually was Java EE/Spring app with Hibernate, but most of these will work for everybody.

As the main tool, we used Ora2PG. That is an open-source tool that worked quite well for us. This article is not describing it’s used. There is a lot of there on the internet.

As we use Hibernate (an ORM framework) most of our issues were mitigated by that, but as every bigger project there still were a lot of SQL queries in the code directly. Our main goal was to have an app that can run on both on Oracle and on PG so that we can work on the migration and still release new features in the meantime.

Tips to write your queries compatible

Problem: TRUNC(SYSDATE —t.num_of_days)

Solution: TRUNC(SYSDATE —t.num_of_days * INTERVAL ‘1’ DAY)

Problem: Don’t use NVL, DECODE.

Solution: COALESCE, CASE statement

Problem: select * from (select * from B); — Inner selects have to have aliases in PG.

Solution: select * from (select * from B) as innerSelect;

Problem: SYSDATE

Solution: NOW()

Problem: random

Solution: random()

Problem: sequenceName.nextval

Solution: select getNextVal('sequenceName') from dual getNextVal function needs to be created in Oracle first

Problem: Oracle is case insensitive for column names. PG not. When calling query and then getting value by get(“columnName”)

Solution: Always use lowercase.

Problem: using UNIQUE

Solution: using DISTINCT instead

Problem: Numbers retrieved from Hibernate used to be Long / BigDecimal now BigInteger, … This applies to count(*) queries as well.

Solution: Always retrieve Number interface and use result.longValue() or similar to get anticipated value.