Conversion of view is an important part of Oracle to PostgreSQL database migration. Considering the fact that syntaxes of queries in Oracle and PostgreSQL aren’t similar and even these two DBMS possess distinguished sets of inbuilt features, it is important to transform SQL statement of every query prior to moving it to the destination database.
Oracle offers several features for developing views which aren’t recognized by PostgreSQL. These features needs to be eliminated throughout conversion:
- DEFAULT
- FORCE / NO FORCE
- WITH CHECK OPTION
- WITH OBJECT IDENTIFIER
- WITH READ ONLY
- UNDER
- XMLType views are not supported by PostgreSQL
Oracle has a unique one-row and one-column table DUAL to manage some queries which does not call for any table, for instance:
SELECT 1 FROM DUAL;
PostgreSQL does not have such table, so it must be created for possible use in queries:
create table dual (varchar(1) not null );
insert into dual(dummy) values(‘x’);
Additionally, all embedded Oracle features that are missing in PostgreSQL need to be replaced by the right equivalents.
- Oracle function CURTIME() is replaced by LOCALTIME or CURRENT_TIME in PostgreSQL
- All occurrences of DAY($a) or DAYOFMONTH($a) must be converted into the following expression EXTRACT(day from date($a))::integer
- Function DateAdd($Date, $Format, $Days, $Months, $Years) can be converted into this expression:
Date + cast(‘$Days day’ as interval) + cast(‘$Months months’ as interval) + cast(‘$Years years’ as interval)
- Oracle function DECODE does not have direct equivalent in PostgreSQL. It must be replaced by CASE-WHEN-THEN-ELSE expression as illustrated by example below. Oracle expression:
SELECT colorid, DECODE(colorid, ‘1’, ‘white’, ‘2’, ‘black’, 3, ‘red’, NULL) AS ‘colorname’ FROM colors
In PostgreSQL the same query should be 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
- Simplified format of using Oracle function INSTR($str1, $str2) can be replaced by POSITION($str2 in $str1). More detailed porting of this function is clearly started below
- LCASE is Oracle function that makes string lowercase, it must be converted into LOWER in PostgreSQL
- All occurrences of LOCATE($str1,$str2), Oracle function searching occurrence of $str1 int $str2, must be replaced by POSITION($str1 in $str2) in PostgreSQL
- Function SUBSTR($string, $from, $for) is converted into SUBSTRING($string, $from, $for)
- NVL($a, replace_with) can be converted into COALESCE($a, replace_with)
- Oracle function RAND must be replaced by PostgreSQL equivalent RANDOM
- Regexp support function REGEXP_LIKE($string, $pattern) is converted into PostgreSQL expression $string LIKE $pattern
- All occurrences of SYSDATE must be replaced by PostgreSQL equivalent CURRENT_DATE or NOW()::date
- Oracle function UCASE, Oracle function that makes string uppercase, is converted into PostgreSQL synonym UPPER
- All occurrences of 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. In order to get version-independent solution, the following expression may be used:
SELECT md5(random()::text || clock_timestamp()::text)::uuid
- WEEK($a) is converted into EXRACT(week from date($a))
- Oracle function YEAR($a) must be replaced by EXRACT(year from date($a))