Convert Queries from Oracle to PostgreSQL

The process of database migration from Oracle to PostgreSQL includes converting queries that are part of views, stored procedures or triggers. This stage may require human processing because syntax of queries in Oracle and PostgreSQL is not the same and there is no direct equivalent for some patterns or inbuilt functions. This article explores conversion of most important patterns used in Oracle SQL 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’);

All built-in Oracle functions missing in PostgreSQL must be replaced by the appropriate equivalents:

  1. CURTIME() is replaced by LOCALTIME(0)
  2. DAY($a) or DAYOFMONTH($a) is replaced by expression EXTRACT(day from date($a))::integer
  3. 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)

  1. DECODE() is replaced by CASE-expression as follows

SELECT colorid,

CASE colorid

WHEN ‘1’ THEN ‘white’

WHEN ‘2’ THEN ‘black’

WHEN ‘3’ THEN ‘red’

ELSE NULL

END AS ‘colorname’

FROM colors

  1. INSTR($str1, $str2) is replaced by POSITION($str2 in $str1)
  2. LCASE($str) is replaced by LOWER($str)
  3. LOCATE($str1,$str2) is replaced by POSITION($str1 in $str2)
  4. SUBSTR($string, $from, $for) is replaced by SUBSTRING($string, $from, $for)
  5. NVL($a, $replace_with) is replaced by COALESCE($a, $replace_with)
  6. RAND is replaced by RANDOM
  7. REGEXP_LIKE($string, $pattern) is replaced by expression $string LIKE $pattern
  8. SYSDATE is replaced by CURRENT_DATE
  9. UCASE($str) is replaced by UPPER($str)
  10. SYS_GUID() can be replaced by uuid_generate_v1(). In early versions of PostgreSQL (before v9.4) this function depended on the OSSP UUID library, the following expression may be used to avoid such dependence:

SELECT md5(random()::text || clock_timestamp()::text)::uuid

  1. WEEK($a) is replaced by EXRACT(week from date($a))
  2. YEAR($a) is replaced by EXRACT(year from date($a))

This brief guide on converting queries from Oracle to PostgreSQL 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 PostgreSQL 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 PostgreSQL converter also handles migration of table definitions, indexes, constraints and data.

Leave a Reply

*