Home > Documentation > Oracle to PostgreSQL
Convert Oracle views into PostgreSQL format |
[Oracle to PostgreSQL Converter] [About Migration] [Types Mapping] [Stored Procedures] |
Database migration from Oracle to PostgreSQL involves converting Oracle views into the destination format. Since syntax of queries in Oracle and PostgreSQL is not identical and also these two DBMS have distinguished sets of built-in functions, it is necessary to convert each SQL statement before passing it to the destination DBMS.
Oracle provides multiple features for creating views that are not supported by PostgreSQL. Such features must be removed during conversion:
Also, Oracle supports custom syntax of LEFT JOIN that is not accepted by PostgreSQL:
SELECT t1.f2, t2.f2 FROM t1, t2 WHERE t1.f1=t2.f1 (+)
Such queries must be converted according to ANSI SQL standard on LEFT JOIN:
SELECT t1.f2, t2.f2 FROM t1 LEFT JOIN t2 ON t1.f1=t2.f1
Finally, all embedded Oracle functions that are missing in PostgreSQL must be replaced by the appropriate equivalents.
CURTIME()
is replaced by LOCALTIME(0)
in PostgreSQLDAY($a)
or DAYOFMONTH($a)
must
be converted into the following expression: EXTRACT(day from date($a))::integer
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)
INSTR($str1, $str2)
can be
replaced by POSITION($str2 in $str1)
.LCASE
must be converted into PostgreSQL equivalent LOWER
LOCATE($str1,$str2)
must be replaced by POSITION($str1 in $str2)
MINUTE($a)
is converted into EXTRACT(minute from $a)
MONTH($a)
is converted into EXTRACT(month from $a)
NVL($a, replace_with)
can be converted into COALESCE($a, replace_with)
RAND
must be replaced by PostgreSQL equivalent RANDOM
REGEXP_LIKE($string, $pattern)
is converted into
PostgreSQL expression $string LIKE $pattern
SECOND($a)
is converted into EXTRACT(second from $a)
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
UCASE
is converted into UPPER
that is synonym in PostgreSQLWEEK($a)
is converted into EXTRACT(week from $a)
YEAR($a)
must be replaced by EXTRACT(year from date($a))
where rownum <= 1000
is
replaced by limit 1000
in PostgreSQL. Visit this page
to learn more about of PostgreSQL equivalents of ROWNUM
.
Click here to learn about migrating hierarchical queries from Oracle to PostgreSQL.