database migration - yibinericxia/documents GitHub Wiki

1. Tools

• JOOQ
• Ora2Pg

2. SQL

• nvl() -> coalesce() and pay attention to the empty string which is handling differently between Oracle and Postgres
• sequence.nextval -> nextval('sequence') and its type: BigDecimal -> BigInteger
• rownum -> limit or others
• trunc(date) -> date_trunc('day', date)
• listagg() -> string_agg(), or array_to_string() with array_agg()
• sysdate -> current_date/current_timestamp/now()
• merge -> insert on conflict
• PL/SQL -> PL/pgSQL
• substr/dbms_lob.substr() -> substr()
• use quotes on table/column names for case-sensitive cases. By default Oracle converts all names into uppercase and Postgres converts them into lowercase, so it is better to make table/column names case insensitive without quotes.

3. Java PreparedStatement & ResultSet methods during Oracle CLOB/BLOB -> Postgres TEXT/BYTEA

• Clob -> InputStream in DTO class (or String with columnDefinition = "TEXT" in Entity class) & Reader for the input stream (or string)
• use g(s)etCharacterStream() instead of g(s)etClob()
• Blob -> InputStream in DTO class (or byte[] with columnDefinition = "BLOB" in Entity class)
• use g(s)etBinaryStream() instead of g(s)etBlob()

4. Move large files in Oracle database to lower-cost storage

5. Need to use GROUP BY clause for any column in aggregate function or having functional dependency

Any column in the aggregate function or having functional dependency used in SELECT list expression needs to put into the GROUP BY clause.

6. Sequence Cacheing

Oracle shares the same cache among all the db sessions, but Postgres will pre-allocate sequence cache per session. Oracle and Postgres handle shutdown differently as well. So if your logic design which depends on the order of sequence with cache size more than 1 may need to change.

7. Automatic Type Conversions

Oracle will do automatic conversion of datatype when it makes sense, for example when sql statement expects a number but gets a string. This implicit data conversion will impact the performance as the index on the column of conversion will not be used and performance could be hit badly, particularly in PL/SQL procedure.

Postgres treats SQL as a type language strongly and takes the automatic conversion more seriously, so changes for correct datatype usage will be needed if the above scenario happens in database migration.

• https://www.enterprisedb.com/blog/the-complete-oracle-to-postgresql-migration-guide-tutorial-move-convert-database-oracle-alternative