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:

DEFAULT
FORCE / NO FORCE
WITH CHECK OPTION
WITH OBJECT IDENTIFIER
WITH READ ONLY
UNDER
XMLType views are not supported by PostgreSQL

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

Oracle limitation by number of returned rows like where rownum <= 1000 is replaced by limit 1000 in PostgreSQL. Visit this page to learn more about of PostgreSQL equivalents of ROWNUM

Finally, all embedded Oracle functions that are missing in PostgreSQL must be replaced by the appropriate equivalents as it is described in this article.

Click here to learn about migrating hierarchical queries from Oracle to PostgreSQL.