Home > PostgreSQL > Features Compatibility with Oracle

Features Compatibility with Oracle

A few days back I was given the task to write a document giving the compatibility features in PostgreSQL when comparing with Oracle as a prospective customer wanted to move out of Oracle. They gave us a list of features for which we had to give some sort of compatibility in PostgreSQL.

Here is how I ended up doing it:



Procedural Language

Supported using PLPGSQL which is very simiar to PLSQL in Oracle

Windowing Functions

RANK, OVER, PARTITION BY, WITH supported for Recursive queries

Full Text Search

Proper linguistic, searching and ordering support for natural language documents

Table Paritioning

Range and list paritioning is supported


Oracle like tablespaces support

Database Links

Supported using “dblinks” contribution module


Provides vast majority of character sets including multi-byte ones.


ACID compliant and uses MVCC

GIS Support

Supported with PostGIS which uses GEOS 3.1

Query Optimization

Cost based query optimization

Large Objects Support

Options available for Oracle like BFILE, BLOB and RAW data types using pre-defined large object methods

XML Support

XML data type supported

Application Drivers

Perl, Java, C/C++, PHP .NET, Python, TCL, Delphi, Ruby, ODBC, OLEDB and Openoffice drivers

Embedded SQL

Using ECPG

Process Monitoring

Using tools like ‘ptop’

Replication and Load Balancing

Using Slony with PgPool

Recovery Mecahnism

With PITR (Point In Time Recovery)

Backup and Restore

Standard backup and restore procedures for database using pg_dump and pg_restore

Access Control

Done by controlling a configuration file where Operating System level authentication can also be used

Gateway Product support

Using DBI-Link it is possible to connect to any datasource from within PostgreSQL


Supported on most of the Unix based platforms (Solaris, RHEL, Ubuntu, SuSe, FreeBSD and many more) and Windows

Shoaib Mir

  1. Slava
    March 24, 2009 at 5:34 am

    this is pretty useless unless you provide more details on implementation. E.g. dblink implementation in Oracle is much more advance than in Postgres (you can’t mix local and remote query in one sql which makes it useless in 90% of cases). There is also no cold backup (rman) alternative in postgres other than os-native.

  2. March 24, 2009 at 9:18 am

    Well I agree with what you are saying, but this was only meant to give the users a general overview not a detailed one which is why you dont find any implementation details. I will be covering it sometime later as I start preparing that 🙂

  3. Slava
    March 24, 2009 at 4:37 pm

    Thank you, will be looking forward.

  4. March 25, 2009 at 1:25 am

    On the one hand, I agree with Slava that for purposes of compatibility, this list isn’t terribly helpful, since many of the features in Postgres and not compatible with Oracle, even though they allow you to accomplish the same goals (Think CONNECT BY vs. WITH RECURSIVE)

    On the other hand, and this is what I tell me customers when doing Oracle conversions, generally the tools Postgres gives you can help you accomplish the same goals as you would using Oracle’s tools; for example what are you really accomplishing with cold backup?

    Also, you have to remember that just because you are familiar with a tool, doesn’t mean it is better: I like the syntax of oralink more than dblink (and you can mix remote/local queries, it just isn’t always wise to do so, just like in Oracle), but if you want to do something like connect and execute a remote function one a non-native database, dblink is a whole lot better for that task.

  5. March 25, 2009 at 10:51 am

    This list was actually just a general overview so not really an extensive one, this is why I didnt cover all the details but yes I have done a number of Oracle conversions and I found PostgreSQL to be really handy in almost every case where PostgreSQL tools were able to do what I needed and the community help in that process had always been very handy with giving the work-arounds.

  6. Lou
    March 26, 2009 at 7:23 am

    A couple of points.

    First: v8.4 is in beta, and will be released soon. Several of these list items are included or expanded upon in 8.4. Ref http://www.postgresql.org/about/featurematrix.html#1

    Second. Take a look at EnterpriseDB – This version of PostgreSQL has had a lot of Oracle compatibility added to it.

  7. March 26, 2009 at 10:10 am

    >>First: v8.4 is in beta, and will be released soon. Several of these list items are included or expanded upon >>in 8.4. Ref http://www.postgresql.org/about/featurematrix.html#1

    Yes, I have already used them from versions out of the CVS and very happy with everything, cant wait for the release itself. This is why I added them in the list.

    >>Second. Take a look at EnterpriseDB – This version of PostgreSQL has had a lot of Oracle compatibility added >>to it.

    I worked for EnterpriseDB for more then two years from the very start back in end of 2005 🙂 I know they are very much Oracle compatible but the client I am working with is much more interested in standard PostgreSQL and not the modified version.

  8. April 10, 2009 at 4:37 pm


  9. John
    January 8, 2010 at 6:41 am

    For us, the stumbling block is always our large base of 3rd party oracle forms. If only there was a suitable answer for that.

    • January 8, 2010 at 8:44 am

      not really an expert on the forms but I guess you can use ODBC in there or the best bet if possible to convert them to JSP pages. I cant really recall the exact name of the product but there used to be one which can take your Oracle forms and convert them to JSP pages.

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: