1. Home
  2. Docs
  3. Oracle to PostgreSQL Migration
  4. Built-in Functions
  5. Inconsistent Functions

Inconsistent Functions

This topic lists out the built-in functions that are available in both Oracle and PostgreSQL databases which do not behave exactly in the same way. There are situations when the result matches in both databases for the same input. However you can not guarantee the same output in both the databases every time.

So, it is crucial to understand the behavior of functions in both the databases and always be careful while doing migration.

LENGTH Function

OraclePostgreSQL

LENGTH function in Oracle takes a string or numeric value as argument and returns the length. If the argument is NULL then LENGTH function returns NULL. White space is a valid character and LENGTH function counts white spaces as well.

Syntax:
LENGTH(Character String) or LENGTH(Numeric)
SELECT length('Database Migration') str_length FROM DUAL;

STR_LENGTH
----------
	18

If you supply a numeric value to LENGTH, then Oracle returns the length of digits present in the input.

SELECT length(123456) num_length FROM DUAL;

NUM_LENGTH
----------
	 6

When the argument is NULL, LENGTH function returns NULL. NULL and empty string in Oracle both are treated as NULL. So, both returns NULL when given as argument to LENGTH.

SELECT length(NULL) num_length FROM DUAL;

NUM_LENGTH
----------


SELECT length('') num_length FROM DUAL;

NUM_LENGTH
----------

LENGTH function in PostgreSQL is a string function. LENGTH function returns the length of characters in the input string. If the argument is NULL, then LENGTH returns 0. White space is a valid character and LENGTH function counts white spaces as well.

Syntax:
LENGTH(Character String)
SELECT length('Database Migration') str_length;

 str_length 
------------
         18

If you supply a numeric value to LENGTH, then PostgreSQL throws an error.

SELECT length(123456) num_length;
ERROR:  function length(integer) does not exist
LINE 1: SELECT length(123456) num_length;
               ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

Type cast the input argument to TEXT or CHARACTER VARYING (VARCHAR) when operating with numeric input to LENGTH function in PostgreSQL.

SELECT length(123456::text) num_length;

 num_length 
------------
          6


SELECT length(123456::varchar) num_length;

 num_length 
------------
          6

NULL and empty string in PostgreSQL are handled differently. When the argument is NULL, LENGTH function returns NULL. When the argument is empty string, LENGTH function returns 0.

SELECT length(NULL) num_length;
 num_length 
------------
           

SELECT length('') num_length;

 num_length 
------------
          0

CONCAT Function

OraclePostgreSQL

CONCAT function in Oracle takes two strings as arguments and returns String1 concatenated with String2 as the result.

Syntax:
concat(str1, str2)

If you supply one argument to CONCAT, then Oracle reports error as it is meaningless.

SELECT concat('Database') str_concat FROM DUAL;

ERROR at line 1:
ORA-00909: invalid number of arguments

CONCAT in Oracle can only work with two input arguments.

SELECT concat('Database', 'Migration') str_concat FROM DUAL;

STR_CONCAT
-----------------
DatabaseMigration

If you attempt to give more than 2 input parameters to CONCAT, then Oracle returns error.

SELECT concat('Database', ' ', 'Migration') str_concat FROM DUAL;

ERROR at line 1:
ORA-00909: invalid number of arguments

If you have more than 2 strings to be concatenated, you can cascade CONCAT function.

SELECT concat(concat('Database', ' '), 'Migration') str_concat FROM DUAL;

STR_CONCAT
------------------
Database Migration


SELECT concat(concat(concat(concat('Database', ' '), 'Migration'), ' - '), '1') str_concat 
  FROM DUAL;

STR_CONCAT
----------------------
Database Migration - 1

CONCAT function in PostgreSQL can take any number of input strings and returns all input strings concatenated as a single string. The concatenation happens from left to right.

Syntax:
concat(str "any" [, str "any" [, ...] ])

If you supply one argument to CONCAT function in PostgreSQL, then the input string is returned. PostgreSQL does not report any error although one input string with CONCAT is meaningless.

SELECT concat('Database') str_concat;

 str_concat 
------------
 Database

String concatenation happens with more than one input arguments.

SELECT concat('Database', 'Migration') str_concat;

    str_concat     
-------------------
 DatabaseMigration

CONCAT in PostgreSQL can work for any number of parameters. So, you need not use cascade of CONCAT. But, cascade of CONCAT is also supported in PostgreSQL.

SELECT concat('Database', ' ', 'Migration') str_concat;

    str_concat     
-------------------
 DatabaseMigration


SELECT concat(concat('Database', ' '), 'Migration') str_concat;

     str_concat     
--------------------
 Database Migration


SELECT concat('Database', ' ', 'Migration', ' - ','1') str_concat;

       str_concat       
------------------------
 Database Migration - 1
Was this article helpful to you? Yes No

How can we help?