SUBSTR Functionality Differences Between Oracle and PostgreSQL: What You Need to Know

Migrating away from Oracle can be both fun and challenging. One of the core functionality you may encounter while moving away from Oracle is the usage of string-related functions, such as SUBSTR, INSTR, and CONCAT. These functions are widely used in the Oracle ecosystem, but they may not be compatible with open-source options like PostgreSQL.

While similar functions may be available in PostgreSQL, they may differ in functionality and input argument support.

In this blog, we will explore the differences between the SUBSTR function in Oracle and PostgreSQL when migrating away from Oracle. It is important to consider the impact of string manipulation functions, as they are often embedded as part of application SQL and can affect the overall migration pattern for both the database and the application logic.

Oracle substr vs PostgreSQL substr

It’s critical to understand difference between substr implementation on the database engine. Below tabular representation gives a comparative view.

QueryOracle outputPostgreSQL output
substr(‘abcdefgh’,0,1)a” (Empty String)
substr(‘abcdefgh’,-3,1)f” (Empty String)
substr(‘abcdefgh’,-10,1)NULL” (Empty String)
substr(‘abcdefgh’,1,-1)NULLERROR: negative substring length not allowed
substr(‘abcdefgh’,-8,4)abcd” (Empty String)
substr(‘abcdefgh’,10,1)NULL” (Empty String)
Oracle vs PostgreSQL – Substr function

let’s break it down to note the core difference.

  • In Oracle, if the start position argument is 0, it is treated as 1, which creates a difference compared to PostgreSQL.
  • Oracle supports negative start position arguments, but PostgreSQL will always return an empty string.
  • PostgreSQL will throw an exception if the substring length is negative, while Oracle will return NULL.
  • If the negative start position argument has a value greater than the string length, Oracle will return NULL.

    Now, it is fair to say that substr functions in PostgreSQL is not directly compatible with Oracle. so what option we have to have compatible option when migrating away from Oracle.

    Orafce extension provides inbuilt wrapper with functionality compatible with Oracle substr.

    orafce – substr functionality

    let’s re-validate the initial tabular comparision with Orafce Extension – Substr

    QueryOracle outputOrafce – Substr
    substr(‘abcdefgh’,0,1)aa
    substr(‘abcdefgh’,-3,1)ff
    substr(‘abcdefgh’,-10,1)NULL” (Empty String)
    substr(‘abcdefgh’,1,-1)NULLNULL
    substr(‘abcdefgh’,-8,4)abcdabcd
    substr(‘abcdefgh’,10,1)NULL” (Empty String)
    Oracle vs Orafce – Substr function

    Orafce extension provides a substr wrapper within oracle schema that is mostly compatible but not fully. Empty string and NULL are two different values in PostgreSQL, if interested in more insight do check out blog in the tweet.

    Mitigation on resolving difference between Oracle and PostgreSQL on returning empty string and NULL will involve creating additional wrapper function in User Schema and use it as replacement on every occurrence of SUBSTR.

    CREATE FUNCTION user1.substr(str text, start integer, len integer) RETURNS text
        LANGUAGE sql IMMUTABLE PARALLEL SAFE
        AS $_$
        SELECT nullif(oracle.substr($1,$2,$3),'');

    We can also use NULLIF on every occurrence of SUBSTR as migration pattern.

    QueryOracle outputWrapper – user1.substr
    substr(‘abcdefgh’,0,1)aa
    substr(‘abcdefgh’,-3,1)ff
    substr(‘abcdefgh’,-10,1)NULLNULL
    substr(‘abcdefgh’,1,-1)NULLNULL
    substr(‘abcdefgh’,-8,4)abcdabcd
    substr(‘abcdefgh’,10,1)NULLNULL
    Oracle vs Wrapper – Substr function

    Overwriting pg_catalog is not supported as default, we will need to change the search path to use wrapper code wither in oracle schema provided by Orafce or custom wrapper on top of Orafce.

    set search_path =user1,pg_catalog,public;
    
    select substr('abcdefgh',-3,1), pg_catalog.substr('abcdefgh',-3,1);

    Orafce ease out overall need of compatible functions from Oracle and in case need arise we can overrite it by creating wrapper functions in users schema.

    About Deepak Mahto

    Database Guy with expertise in database performance, migration and Cloud Adoption.
    This entry was posted in Oracle Internal, Oracle to, Oracle to PG migration, postgresql and tagged , , , , . Bookmark the permalink.

    2 Responses to SUBSTR Functionality Differences Between Oracle and PostgreSQL: What You Need to Know

    1. Pingback: Scaling Postgres Episode 248 Pagination Solution, Return Modification, Insert Deletions, How To JSON – 53GB

    2. Bindu Muralidharan says:

      Could you give us best practices for migrating from Informix to Postgresql, especially the Blob data in informix..

      Like

    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 )

    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