Migrate Oracle SQL to PostgreSQL

Proprietary

Converts Oracle-specific SQL syntax and functions to PostgreSQL equivalents. SQL whose conversion depends on column types, such as TRUNC(hire_date) or hire_date + 1, is left for PostgreSQL to reject, as are MONTHS_BETWEEN, NEXT_DAY, LAST_DAY, CONNECT BY and (+) outer joins. Oracle treats an empty string as NULL and PostgreSQL does not, so an empty result, such as from SUBSTR or CONCAT, is not NULL in PostgreSQL. A one-argument TO_CHAR becomes a cast to TEXT, which prints 0.5 and a column's 1.10 where Oracle prints .5 and 1.1. A four-argument REGEXP_REPLACE, whose last argument Oracle reads as a start position, is left as it is, and PostgreSQL 16 and later replace only its first match.

org.openrewrite.sql.MigrateOracleToPostgres
mod run . --recipe=org.openrewrite.sql.MigrateOracleToPostgres

Install it once from Recipe actions, then run it on every repository under the current directory. New to the Moderne CLI? Set it up.

Recipe list

It runs these recipes, in this order.

  1. Convert days added to a date to an INTERVAL
  2. Convert || to CONCAT
  3. Convert Oracle functions to PostgreSQL
  4. Convert Oracle data types to PostgreSQL
  5. Convert MINUS to EXCEPT
  6. Convert SYSDATE and SYSTIMESTAMP to standard SQL
  7. Convert NEXTVAL and CURRVAL pseudocolumns to function calls
  8. Convert a ROWNUM bound to LIMIT
  9. Remove FROM DUAL
  10. Format SQL in string text blocks