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_nameOracle 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 |