|| String Concatenation Operator - Oracle to PostgreSQL Migration

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

In PostgreSQL, the || operator returns NULL if any operand is NULL. To avoid this, use the CONCAT function, which ignores NULL values, or use COALESCE to replace NULLs 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.