Porting applications from Oracle to PostgreSQL
If you are planning to migrate a database application from Oracle to PostgreSQL, this article can help you to understand and solve some of the issues occurring in such a process. PostgreSQL has recently become feature-complete in many areas and therefore porting projects from Oracle might be a feasible option in much more cases. Oracle and PostgreSQL both conform to standard SQL. However, they contain several extensions and implementation details that differentiate one from the other. Here is table with some of the differences and how to deal with them.
| Oracle 10g | Postgres 8.4 | Observations |
|---|---|---|
| char(size) | char(size) | Oracle treats a character value with a length of zero as null. This “weird and inconsistent” behavior is not true for PostgreSQL. Consequently, the ” string must be replaced with NULL. |
| number(p,s) | NUMERIC(precision, scale) | For performance reasons, number (p, s) should be replaced with INTEGER, when scale is zero. |
| varchar2 | varchar or text | |
| date | date | SYSDATE → current_timestamp or now() |
| clob | text | |
| sysdate | current_timestamp | |
| decode (expression, search, result [, search, result]… [, default] ) |
CASE expression WHEN value THEN result [WHEN ...] [ELSE result] END or CASE WHEN condition THEN result [WHEN ...] [ELSE result] END |
The general rule is that NULL is not equal to NULL. But, as an exception, in a DECODE function Oracle considers two nulls to be equivalent. If expression is null, then Oracle returns the result of the first search that is also null (nulls are considered equal in this comparison). This is not true for the SQL-compliant conditional expressions available in PostgreSQL. Therefore, based on the possible values for ‘expression’ and ‘search’, the decode replacement is one of the following: |
| Outer JOIN | Outer JOIN | PostgreSQL only supports the SQL-standard outer join syntax. But it is quite easy to translate from the Oracle-specific syntax. For example, SELECT * FROM a, b WHERE a.x = b.y(+) becomes SELECT * FROM a LEFT JOIN b ON a.x = b.y |
| Analytic Functions | Window Functions | Both the Oracle Analytic functions and Postgres Window functions allow you to rank a data set and work with its top-ranked or bottom-ranked rows. A lot of extra work is required because the implementations are very different. Moreover, in PostgreSQL, unlike Oracle, if there is a need to filter or group rows after the window calculations are performed, you have to use a sub-select like in the following simple example: |
| Functions (Oracle’s PL/SQL language) | Functions (PostgreSQL’s PL/pgSQL language) | Need to register a new language:CREATE LANGUAGE plpgsql;In PostgreSQL, the function body is considered to be a string literal, so you need to use quote marks or dollar quotes around it; RETURN key word in the function prototype (not the function body) becomes RETURNS in PostgreSQL; join and cursors syntax must be adjusted, etc. |
| Months_Between (Oracle/PLSQL: Function) |
No direct replacement is available; but a similar function can be implemented in PostgreSQL’s PL/pgSQL language. Oracle’s MONTHS_BETWEEN (date1, date2) function returns the number of months between dates date1 and date2. This number is computed using a quite intricate algorithm. |
|
| || Operator | || Operator | An important aspect here is that in PostgreSQL (unlike Oracle) concatenation with a null column nulls the result. If this is not be the behavior you expect, there are several solutions to handle this situation including the use of the SQL CASE WHEN expression, COALESCE or ARRAY_TO_STRING built-in functions.You can also make use of the PostgreSQL extensibility and create a custom version of the CONCAT operator. |
| ALTER SESSION SET CURRENT_SCHEMA = … |
SET search_path TO … | To permanently alter the search path set on each connection, use:ALTER USER user_name SET search_path to ...;This change will only take effect after reconnecting to the database. |
| date (data type);
timestamp (data type); to_char(timestamp, text) function; sign(number) function; type casts; substr(string, from [, count]) function; ‘FROM dual’ syntax; |
Porting is straightforward |
Tags: Oracle PostgreSQL
Posted in: Oracle, PostgreeSQL
July 30, 2010
| 
Alexandru Gyulai | 
3 Comments
3 Responses
Porting applications from Oracle to PostgreSQL « DbRunas – Noticias y Recursos sobre Bases de Datos
July 31, 2010 1
[...] http://blog.ropardo.ro/2010/07/30/porting-applications-from-oracle-to-postgresql/ July 30, 2010 // Oracle, PostgreSQL // No Comments // [...]
August 10, 2010 2
Good dispatch and this mail helped me alot in my college assignement. Thanks you as your information.
September 25, 2010 3
Nice fill someone in on and this mail helped me alot in my college assignement. Gratefulness you as your information.
Leave a Reply