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()|
[, search, result]…
[, default] )
WHEN value THEN result
CASE WHEN condition
|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.
|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:
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.
|No direct replacement is available; but a similar function can be implemented in PostgreSQL’s PL/pgSQL language. Oracle’s
||| 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
You can also make use of the PostgreSQL extensibility and create a custom version of the CONCAT operator.
SET CURRENT_SCHEMA = …
|SET search_path TO …||To permanently alter the search path set on each connection, use:
This change will only take effect after reconnecting to the database.
|date (data type);
timestamp (data type);
to_char(timestamp, text) function;
substr(string, from [, count]) function;
‘FROM dual’ syntax;
|Porting is straightforward|
Finally, there’s another very important peculiarity of what does Cialis that brings it so high above its alternatives. It is the only med that is available in two versions – one intended for use on as-needed basis and one intended for daily use. As you might know, Viagra and Levitra only come in the latter of these two forms and should be consumed shortly before expected sexual activity to ensure best effect. Daily Cialis, in its turn, contains low doses of Tadalafil, which allows to build its concentration up in your system gradually over time and maintain it on acceptable levels, which, consequently, makes it possible for you to enjoy sex at any moment without having to time it.
Good dispatch and this mail helped me alot in my college assignement. Thanks you as your information.August 10, 2010 at 1:51 am
Nice fill someone in on and this mail helped me alot in my college assignement. Gratefulness you as your information.September 25, 2010 at 2:05 am