|| String Concatenation Operator - Oracle to PostgreSQL Migration

In Oracle, the || operator concatenates strings ignoring NULL values.

In PostgreSQL, you can also use the || operator to concatenate strings but if any operand is NULL then the result of the concatenation in NULL, so you should use the CONCAT function that ignores NULL values or replace NULL values with empty strings.

Oracle:

   -- Concatenate strings ignoring NULL values
   SELECT 'A' || NULL || 'B' FROM dual;
   /* AB */

PostgreSQL:

   -- You can also use || opertor for non-NULL expressions
   SELECT 'A' || 'B';
   /* AB */
 
   -- But if any operand is NULL, the result is NULL
   SELECT 'A' || NULL || 'B'; 
   /* NULL */
 
   -- So it is better to use CONCAT function that ignores NULL values
   SELECT CONCAT('A', NULL, 'B'); 
   /* AB */
 
   -- Or you can replace NULL values with empty strings
   SELECT 'A' || COALESCE(NULL, '') || 'B';
   /* AB */

For more information, see Oracle to PostgreSQL Migration.