admin
The process of Oracle to Postgres migration includes converting queries that are part of views, stored procedures or triggers. This task may require some manual work of the related specialists because syntax of queries in Oracle and Postgres is not the same and there is no direct equivalent for some patterns or system functions. This article explores Oracle to Postgres migration of common syntax patterns used in queries. It is assumed that target audience has basic knowledge in database programming.
Oracle offers a number of options for composing queries that are not supported by PostgreSQL. These patterns must be omitted during conversion:
- DEFAULT
- FORCE / NO FORCE
- WITH CHECK OPTION
- WITH OBJECT IDENTIFIER
- WITH READ ONLY
- UNDER
Oracle has built-in table DUAL used to compose queries which do not call for any table, for example:
SELECT 1 FROM DUAL;
In PostgreSQL the same table must be created for further use in queries as follows:
create table dual (varchar(1) not null );
insert into dual(dummy) values(‘x’);
Oracle to Postgres migration of built-in functions is implemented according to the following rules:
- CURTIME is replaced by LOCALTIME(0)
- DateAdd($Date, $Format, $Days, $Months, $Years) is replaced by expression
$Date + cast(‘$Days day’ as interval) + cast(‘$Months months’ as interval) + cast(‘$Years years’ as interval)
- DECODE is replaced by CASE-WHEN-ELSE-END expression as follows
- INSTR is replaced by POSITION
- LCASE is replaced by LOWER
- LOCATE is replaced by POSITION
- SUBSTR is replaced by SUBSTRING
- NVL is replaced by COALESCE
- RAND is replaced by RANDOM
- REGEXP_LIKE is replaced by expression $string LIKE $pattern
- SYSDATE is replaced by CURRENT_DATE
- UCASE is replaced by UPPER
- SYS_GUID can be replaced by uuid_generate_v1
- functions DAY, DAYOFMONTH, WEEK, MONTH, YEAR are replaced by EXRACT
This brief guide on Oracle to Postgres migration of queries indicates that it is a tedious and complicated procedure that can take a lot of efforts when doing it manually. It is quite reasonable to use dedicated automation tools for this purpose.
One of such tool is Oracle to Postgres converter developed by Intelligent Converters. This is a software company working in database migration field for more than 15 years. Their tool converts more than 80% of all possible patterns used in SQL SELECT-queries. Oracle to Postgres converter also handles migration of table definitions, indexes, constraints and data.
