|   |   | 
From:       To:      

Convert PostgreSQL queries to Oracle

[PostgreSQL to Oracle Converter]  [Stored Functions]

When migrating databases from PostgreSQL to Oracle server it is often necessary to translate PostgreSQL queries according to Oracle syntax as well. Syntax of SQL queries in PostgreSQL and Oracle are similar but not identical. This article discovers 10 most popular differences between PostgreSQL and Oracle syntax. The target audience for this guide should have general database management knowledge and experience in composing SQL queries.

1. Types casting. Unlike PostgreSQL, Oracle does not apply strict limitations on conformance of source and destination data types. For example, all numeric data can be stored into NUMBER without any casting. However, if text data must be casted to numeric variable, Oracle requires to use CAST function. For example, PostgreSQL code: 

SELECT x::int as xint FROM tbl;

must be converted into Oracle equivalent like this: 

SELECT CAST(x AS INT) FROM tbl;

2. Limit number of output rows. PostgreSQL uses LIMIT X,Y clause to limit the number of rows returned by SELECT-query, where X is offset (number of rows to skip) and Y is number of rows to extract. Oracle offers similar capabilities starting from Oracle 12c through the following statement at the end of SELECT-query: 

OFFSET X ROWS FETCH NEXT Y ROWS ONLY;

For Oracle versions before 12c it is required to compose 2 subqueries in order to limit result with both lower and upper bounds: 

select * from (
    select a.*, ROWNUM rnum from ( 
        {original query goes here} 
    ) a
    where ROWNUM <= Y )
where rnum >= X;

3. DATE_PART PostgreSQL function can be emulated in Oracle as follows: 

CREATE OR REPLACE FUNCTION DATE_PART(units VARCHAR2, dt timestamp) 
RETURN integer 
IS 
	result integer;	
BEGIN
	IF units IN ('year','YEAR') THEN 
		return EXTRACT(year FROM dt);
	END IF;	
	IF units IN ('month','MONTH') THEN 
		return EXTRACT(month FROM dt);
	END IF;	
	IF units IN ('day','DAY') THEN 
		return EXTRACT(day FROM dt);
	END IF;	
	IF units IN ('hour','HOUR') THEN 
		return EXTRACT(hour FROM dt);
	END IF;	
	IF units IN ('minute','MINUTE') THEN 
		return EXTRACT(minute FROM dt);
	END IF;	
	IF units IN ('second','SECOND') THEN 
		return EXTRACT(second FROM dt);
	END IF;	
	IF units IN ('epoch','EPOCH') THEN 
		return 
			EXTRACT(DAY FROM (sys_extract_utc(dt)-TIMESTAMP '1970-01-01 00:00:00 +00:00'))*86400+
			EXTRACT(HOUR FROM (sys_extract_utc(dt)-TIMESTAMP '1970-01-01 00:00:00 +00:00'))*3600+
			EXTRACT(MINUTE FROM (sys_extract_utc(dt)-TIMESTAMP '1970-01-01 00:00:00 +00:00'))*60+
			EXTRACT(SECOND FROM (sys_extract_utc(dt)-TIMESTAMP '1970-01-01 00:00:00 +00:00'));
	END IF;	
	return 0;
END;
/

4. PostgreSQL provides statement OVERLAY to replace substring with the specified position and length. For example: 

SELECT OVERLAY('This is the second attempt' PLACING 'third' FROM 13 FOR 6);
gives 'This is the third attempt'. In Oracle the same feature can be implemented by this function: 

CREATE OR REPLACE FUNCTION overlay(str1 VARCHAR2, str2 VARCHAR2, start_pos NUMBER, len NUMBER) 
RETURN VARCHAR2
IS
    result VARCHAR(4000);
BEGIN
    -- Copy substring before the start position
    IF start_pos > 1 THEN
        result := SUBSTR(str1, 1, start_pos - 1);
    END IF;
 
    -- Append the replacement value and the remaining part of the string
    result := result || str2 || SUBSTR(str1, start_pos + len);
 
    RETURN result;
END;
/

5. PostgreSQL function AGE returns interval between two timestamps in text form formatted as 'X years Y mons Z days HH:MM:SS.MILLISEC'. In Oracle the same function can be implemented like this: 

CREATE OR REPLACE FUNCTION age(end_ts timestamp, start_ts timestamp) 
RETURN VARCHAR2
IS
    v_years   NUMBER;
    v_months  NUMBER;
    v_days    NUMBER;
    result    VARCHAR2(4000) := '';
BEGIN
    v_years  := trunc(months_between(end_ts, start_ts)/12);
    v_months := trunc(mod(months_between(end_ts, start_ts),12));
    v_days   := trunc(
        sysdate-add_months(start_ts,trunc(months_between(end_ts, start_ts)/12)*12+
            trunc(mod(months_between(end_ts, start_ts),12))
        )
    );	
    IF v_years > 0 THEN
        result := v_years || ' year';
    END IF;
    IF v_years > 1 THEN
        result := result || 's';
    END IF;        
    IF v_months > 0 THEN
        result := result || ' ' || v_months || ' mon';
    END IF;
    IF v_months > 1 THEN
        result := result || 's';
    END IF;        
    IF v_days > 0 THEN
        result := result || ' ' || v_days || ' day';
    END IF;
    IF v_days > 1 THEN
        result := result || 's';
    END IF;            
    
    result := result || ' ' || 
        NVL(LPAD(EXTRACT(HOUR FROM(end_ts - start_ts)),2,'0'),'00') || ':' || 
        NVL(LPAD(EXTRACT(MINUTE FROM(end_ts - start_ts)),2,'0'),'00') || ':' || 
        TRIM(TO_CHAR(EXTRACT(SECOND FROM(end_ts - start_ts)),'00.0000'));
    RETURN result;
END;
/

6. Function SPLIT_PART is used to return particular item from delimited string in PostgreSQL: 

SELECT SPLIT_PART('red,orange,yellow,green,blue', ',', 4);
This call returns 'green'. In Oracle the same feature can be implemented by the following function: 

CREATE OR REPLACE FUNCTION split_part (str VARCHAR2, delim VARCHAR2, num NUMBER)
RETURN VARCHAR2
IS
    start_pos NUMBER := 1;
    end_pos NUMBER;
BEGIN
    IF num > 1 THEN
        start_pos := INSTR(str, delim, 1, num - 1);
        IF start_pos = 0 THEN
            RETURN NULL;
        END IF;
 
        start_pos := start_pos + LENGTH(delim); 
    END IF;
 
    end_pos := INSTR(str, delim, start_pos, 1);
    IF end_pos = 0 THEN
        RETURN SUBSTR(str, start_pos);
    END IF;
 
    RETURN SUBSTR(str, start_pos, end_pos - start_pos);
END;
/

7. Regular expressions. PostgreSQL provides number of regular expression match operators such as ~, ~*, !~, !~*. For example, the following code checks if string is a time in hh:mm:ss format:

IF (str_hours ~ E'^\\d+:[0-5][0-9]:[0-5][0-9]$') THEN
...
END IF;

Oracle provides REGEXP_LIKE function for the same purposes:

IF (REGEXP_LIKE(str_hours,'^[0-9]+:[0-5][0-9]:[0-5][0-9]$')) THEN 
...
END IF;

8. Table aliases. PostgreSQL declares aliases for tables in SELECT-queries as follows: 

FROM table_name AS alias_name
Oracle uses almost the same syntax except the keyword "AS" is not allowed and must be removed.

9. PostgreSQL functions having direct equivalent in Oracle:

PostgreSQL Oracle Description
DATE('text expression') TO_CHAR(number) Convert number to string
LOWER LCASE Convert string into lowercase
NOW() SYSTIMESTAMP Get the current date and time
POSITION(substring IN string) INSTR(string, substring) Get position of substring in string
STRING_AGG(column_name,delim) LISTAGG(column_name, delim) WITHIN GROUP (ORDER BY column_name concatenates values into a string, separated by delimiter
SUBSTRING(string, start, len) SUBSTR(string, start, len) Get a substring of string
TIMEOFDAY() TO_CHAR(SYSTIMESTAMP) Get text representation of the current date and time
UPPER UCASE Convert string into uppercase
Copyright (c) 2001-2024 Intelligent Converters