Convert SQL Function from Oracle to PostgreSQL

The list below illustrates how to convert the most popular Oracle built-in functions into PostgreSQL equivalents:

• ADD_MONTH($date,$n_month) is replaced by the expression $date + $n_month * interval '1 month'
• Oracle function DECODE($exp, $when, $then, ...) must be replaced by PostgreSQL expression CASE $exp WHEN $when THEN $then ... END
• Simple form of using Oracle function INSTR($str1, $str2) can be replaced by POSITION($str2 in $str1).
  More comprehensive porting of this function can be found here
• LAST_DAY($date) must be converted into

  (date_trunc('MONTH', cast($date as date)) + INTERVAL '1 MONTH - 1 day')::date

• Oracle function LCASE must be converted into PostgreSQL equivalent LOWER
• All occurrences of LOCATE($str1,$str2) must be replaced by POSITION($str1 in $str2)
• MONTHS_BETWEEN($date2,$date1) is converted into

  12 * extract(year from age($date2::date,$date1::date)) + 
  extract(month from age($date2::date,$date1::date))

• 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
• Function SUBSTR($string, $from, $for) is converted into SUBSTRING($string, $from, $for)
• All occurrences of SYSDATE must be replaced by PostgreSQL equivalent CURRENT_DATE
• 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

• Oracle function UCASE is converted into UPPER that is synonym in PostgreSQL

Have questions? Contact us