1. Home
  2. Docs
  3. Oracle to PostgreSQL Migration
  4. 1 Database Migration Basics
  5. 1.7 Type Casting

1.7 Type Casting

Type casting refers to data conversion. Normally you see two types of data conversions in Oracle and PostgreSQL.

  • Implicit Type Casting : Database automatically converts a value from one datatype to another when such a conversion makes sense.
  • Explicit Type Casting : For some cases you have to explicitly specify datatype conversions using SQL conversion functions.

Implicit Type Casting

Implicit conversion in Oracle seems similar to PostgreSQL for certain type of SQL operations.

OraclePostgreSQL

When an operation is performed, Oracle database implicitly converts data into its correct type based on the operator and the type of operands.

--VARCHAR is automatically type casted to NUMBER

SELECT 10+'3' res FROM DUAL;

       RES
----------
	13


SELECT 'TRUE' res FROM DUAL
WHERE 1 = '1';

RES
----
TRUE

When you supply valid date format as VARCHAR2, Oracle database implicitly converts VARCHAR2 data type to DATE.

--VARCHAR is automatically type casted to DATE

SELECT * FROM products
WHERE mfg_date = '20-DEC-2018';

PostgreSQL database has the intelligence to implicitly convert data into its correct type based on the type of operation and the type of operands.

--VARCHAR is automatically type casted to INTEGER

SELECT 10+'3' res;

 res 
-----
  13
(1 row)


SELECT 'TRUE' res
WHERE 1 = '1';

 res  
------
 TRUE
(1 row)

When you supply valid date format as CHARACTER VARYING, PostgreSQL database implicitly converts CHARACTER VARYING data type to DATE.

--VARCHAR is automatically type casted to DATE

SELECT * FROM products
WHERE mfg_date = '2018-12-20';

However, you can observe a huge difference in PostgreSQL from Oracle with respect to Implicit conversion when you invoke a function.

OraclePostgreSQL

NUMBER data type is implicitly converted to VARCHAR2 for a character function in Oracle.

select length('1234') res FROM DUAL;

       RES
----------
	 4
select length(1234) res FROM DUAL;

       RES
----------
	 4

Function calling in PostgreSQL requires you to explicitly type cast to proper data type.

select length('1234') res;

 res 
-----
   4
(1 row)

PostgreSQL throws error when you supply an integer value for a character function.

select length(1234) res;

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

In those cases you have to explicitly type cast the function arguments.

select length(1234::varchar) res;

 res 
-----
   4
(1 row)

Explicit Type Casting

You use conversion functions for explicit type casting.

OraclePostgreSQL

Explicit type casting in Oracle database is done through any of the following conversion functions.

  • TO_CHAR
  • TO_DATE
  • TO_NUMBER
  • CAST

Using TO_CHAR function to explicitly type cast.

select length(to_char(1234)) res FROM DUAL;

       RES
----------
	 4

Using CAST function to explicit type cast.

select length(CAST (1234 AS VARCHAR2(10))) res FROM DUAL;

       RES
----------
	 4

PostgreSQL database also contains following conversion functions. But the syntax and usage of these functions are different than Oracle. All the following function requires the format specifier as the 2nd argument, where as in Oracle format is not mandatory.

  • TO_CHAR
  • TO_DATE
  • TO_NUMBER

TO_CHAR function to explicit type cast.

select length(to_char(1234)) res;

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

TO_CHAR, TO_DATE, TO_NUMBER functions in PostgreSQL require the format specifier as the 2nd mandatory argument.

select length(to_char(1254, '9999')) res;

 res 
-----
   5
(1 row)

The reason why length(TO_CHAR(1234, ‘9999’)) returned 5 is, PostgreSQL database reserves the starting digit for the sign. The sign with 1234 makes the length to 5.

So, what is the best way to explicit type cast values in PostgreSQL?

Ans: You can use CAST function or type cast operator “::” provided by PostgreSQL database.

CAST is an ANSI complaint function. So, usage of CAST remains same in both Oracle and PostgreSQL. During code migration from Oracle to PostgreSQL, you only need to verify the data type and modify as required.

Using CAST function to explicit type cast.

select length(CAST (1234 AS VARCHAR(10))) res;

 res 
-----
   4
(1 row)

PostgreSQL database also provides (::) type cast operator to explicitly type cast from one data type to another supported data type.

select length(1234::varchar) res;

 res 
-----
   4
(1 row)
Was this article helpful to you? Yes No

How can we help?