Project

General

Profile

Converting to PostgreSQL

Differences from Oracle

  • Behaviour of unique compound indexes containing nulls. Oracle considers rows containing NULLs to be the same (providing they have at least one non-NULL value). Postgres considers them distinct.
  • Zero length strings. Oracle zero length strings are the same as NULL. Postgres distinguishes the two. This could break some "not null" columns as they will now accept empty strings which they didn't before.
  • Temporary tables. Oracle temp tables are schema objects. Postgres destroys temp tables at the end of the current session (or transaction), so they must be recreated every time.
  • Server side cursors. All Oracle cursors behave like Postgres named cursors. We use this in a few places to avoid having to accumulate all results in large queries before returning the results to the client.
  • Sequences numbers in Oracle by default have a maxvalue of 10e27-1 and are stored in a number(38,0) field. In Postgres, the default maxvalue of a sequence number is (2**63)-1 and are based on BIGINT.(http://www.dba-oracle.com/t_sequences.htm) (http://www.postgresql.org/docs/9.1/static/sql-createsequence.html)