Oracle to PostgreSQL Migration

  2. Home
  4. Docs
  6. Oracle to PostgreSQL Migration
  8. NULL Handling
  10. Converting pipe “||” operator

Converting pipe “||” operator

Migrating Pipe “||” operator which is also called as String concatenation operator is an area which is often neglected and left as is during code migration from Oracle to PostgreSQL. In this chapter we discuss what areas you must focus when migrating “||” operator from Oracle database to PostgreSQL. We demonstrate you, the functional differences of Pipe operator between Oracle and PostgreSQL database and the impact if not handled properly during code migration.

Similarity using Pipe operator in Oracle and PostgreSQL

Pipe operator provides a flexible way for concatenating strings in Oracle and PostgreSQL databases. The result of concatenating two character strings is another character string. When the strings are not NULL, Pipe operator exhibit the same behavior in Oracle database and PostgreSQL database. In the latter sections, we discuss when you are dealing with NULLs, the result of strings concatenation may not be same in Oracle and PostgreSQL.

Following code examples demonstrates similarity using Pipe operator for string concatenation in Oracle and PostgreSQL databases.

OraclePostgreSQL
SELECT 'Sample ' || 'String' AS str FROM DUAL;

STR
-------------
Sample String

SELECT 'This ' || 'is my ' || '1st ' || 'string ' || 'concatenation' AS str FROM DUAL;

STR
-----------------------------------
This is my 1st string concatenation
SELECT 'Sample ' || 'String' AS str;

      str      
---------------
 Sample String
(1 row)

SELECT 'This ' || 'is my ' || '1st ' || 'string ' || 'concatenation' AS str;

                 str                 
-------------------------------------
 This is my 1st string concatenation
(1 row)

Pipe operator functional differences in Oracle and PostgreSQL

String concatenation works similar in both Oracle and PostgreSQL when concatenated strings are not null. With NULL, the behavior varies in PostgreSQL to Oracle.

String concatenation exampleResult in OracleResult in PostgreSQL
'abc' || 'def''abcdef''abcdef'
'abc' || NULL'abc'NULL
'abc' || NULL || 'def''abcdef'NULL
NULL || 'def''def'NULL
NULL || NULLNULLNULL
'abc' || '' || 'def''abcdef''abcdef'
'abc' || coalsece('', ',') || 'def''abc,def''abc,def'
'abc' || '' || 'def' || NULL'abcdef'NULL

Migrating Pipe operator correctly in PostgreSQL

At this point, you must have understood how string concatenation using Pipe operator varies in PostgreSQL that Oracle database. The importance for correctly migrating Pipe operator during Oracle to PostgreSQL database conversion should not be underestimated. Following are some approaches to handle string concatenation correctly and effectively during any Oracle to PostgreSQL database code migration phase.

I. Using NULL handling functions like coalesce

II. Using concat_ws function

III. Extending Pipe operator and building custom function

I. Using NULL handling functions like coalesce

OraclePostgreSQL

In Oracle you do not need NULL handling functions like NVL, COALESCE during string concatenation, as Oracle by default ignores NULLs during string concatenation.

SELECT 'abc' || NULL || 'def' AS res FROM DUAL;

RES
------
abcdef

Oracle treats NULL and empty string equivalent. Both are suppressed and have no effect during string concatenation.

SELECT 'abc' || '' || 'def' AS res FROM DUAL;

RES
------
abcdef

During string concatenation in PostgreSQL using Pipe operator, if any of the string is NULL, the result of string concatenation becomes NULL. So, using NULL handling functions for the variables that might be NULL, is one of the option to get similar output like Oracle.

SELECT 'abc' || NULL || 'def' AS res;

 res 
-----
 
(1 row)

PostgreSQL database treats NULLs and empty strings, both as different. If any of the value is NULL during string concatenation, the result becomes NULL. On the contrary, if any of the value is empty string, then it is ignored.

SELECT 'abc' || '' || 'def' AS res;

  res   
--------
 abcdef
(1 row)

From the above two examples, you can see NULLs in string concatenation make the result NULL whereas empty strings empty strings are suppressed. Converting NULLs to empty string using COALESCE function is one of the way to handle NULLs during string concatenation.

SELECT 'abc' || coalesce(NULL, '') || 'def' AS res;

  res   
--------
 abcdef
(1 row)

Where do you use above approach?

This approach makes more sense when you exactly know which variable might be NULL or whenever you are dealing with string concatenation for simple conditions with less variables. But it is certainly not the suitable and effective approach for concatenating large set of strings because of the following reasons.

  • The converted code will be smelly when you have more columns that can be nullable.
  • Too much of manual effort.
  • Needs more time to add coalesce on each variable and may become even complicated if dealing with dynamic SQL.

II. Using CONCAT_WS function

OraclePostgreSQL

String concatenation in Oracle database is straight forward giving little or no further thoughts on handling NULLs separately. However in other databases like PostgreSQL, this may not be same and explicit NULL handling may be required.

SELECT 'abc' || NULL || 'def' AS res FROM DUAL;

RES
------
abcdef

CONCAT_WS function in PostgreSQL provides a way to concat strings by specified separator. You can ignore NULLs with CONCAT_WS by specifying the separator as empty string.

Synatx:

CONCAT_WS(separator, str_1, str_2, ...str_N)

Below code specifies separator as empty string and illustrates ignoring NULLs during string concatenation using CONCAT_WS. 

SELECT concat_ws('', 'abc', NULL, 'def') AS res;

  res   
--------
 abcdef
(1 row)

III. Extending Pipe operator and building custom function

Creating custom functions or operators in PostgreSQL is not difficult at all. Go through how you can build your custom string concatenation operator for extending pipe operator functionality or creating your own string concatenation operator.

Was this article helpful to you? Yes No

How can we help?