Migrate Oracle SQL to PostgreSQL
ProprietaryConverts 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. MigrateOracleToPostgresmod run . --recipe= org. openrewrite. sql. MigrateOracleToPostgresInstall 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.
- Convert days added to a date to an
INTERVAL - Convert
||toCONCAT - Convert Oracle functions to PostgreSQL
- Convert Oracle data types to PostgreSQL
- Convert
MINUStoEXCEPT - Convert
SYSDATEandSYSTIMESTAMPto standard SQL - Convert
NEXTVALandCURRVALpseudocolumns to function calls - Convert a
ROWNUMbound toLIMIT - Remove
FROM DUAL - Format SQL in string text blocks