Difference between PostgreSQL and Oracle--Functions - Alibaba Cloud Developer Forums: Cloud Discussion Forums

Adolph
Engineer
Engineer
  • UID623
  • Fans4
  • Follows1
  • Posts72
Reads:3804Replies:0

[PostgreSQL Development]Difference between PostgreSQL and Oracle--Functions

Created#
More Posted time:Sep 6, 2016 14:59 PM
PostgreSQL is the most powerful open-source database in the world and has won the favor of more and more organizations and developers in China, featuring widespread application. As PostgreSQL application expands, there are more and more requests for data migration from Oracle to PostgreSQL databases. For data migration between databases, data is first migrated, followed by the SQL statements, stored procedures, sequences, and the switch of data consumption methods between different databases in the program. I wrote some articles on SQL and database object conversion based on my understandings and tests. If there are deficiencies in my articles, your advice is highly welcomed.
1. NULL judgment function
The NULL judgment functions in Oracle are nvl(A,B) and coalesce. nvl(A, B) returns A if it judges A is not NULL, otherwise it returns B. The arguments have to be of the same type, or can be automatically converted to the same type. Otherwise explicit conversion is required. The coalesce arguments can be more than one, and the first non-NULL argument will be returned. When the arguments must be of the same type, no automatic conversion will be performed.
PostgreSQL does not support nvl functions, but it supports coalesce functions. The usage is the same with that in Oracle. You can utilize coalesce to convert nvl and coalesce functions of Oracle. The arguments have to be of the same type, or can be automatically converted to the same type. Otherwise manual conversion is required.
Oracle NULL judgment function
SQL> select * from o_test;

    VALUE1 VALUE2     VALUE3
---------- ---------- --------------
           111111     05-Aug-16
         1            31-Jul-16
         2 222222

SQL> select nvl(value1, 'Hello') value1 from o_test;
select nvl(value1, 'Hello') value1 from o_test
                   *
Error in Line 1:
ORA-01722:  Invalid number

SQL> select nvl(value1, '10000') value1 from o_test;

    VALUE1
----------
     10000
         1
         2

SQL> select nvl(value2, 'Hello') value2 from o_test;

VALUE2
----------
111111
Hello
222222

SQL> select nvl(value3, '2010-1-1') value3 from o_test;
select nvl(value3, '2010-1-1') value3 from o_test
                   *
Error in Line 1:
ORA-01861:  Text and format character strings do not match

SQL> select nvl(value3, to_date( '2010-01-01','YYYY-MM-DD')) value3 from o_test;

VALUE3
--------------
05-Aug-16
31-Jul-16
01-Jan-10

SQL> select coalesce(value1, '10000') value1 from o_test;
select coalesce(value1, '10000') value1 from o_test
                        *
Error in Line 1:
ORA-00932:  Data types are not consistent:  The type should be NUMBER, but CHAR is returned

SQL> select coalesce(value1, 10000) value1 from o_test;

    VALUE1
----------
     10000
         1
         2

SQL> select coalesce(value2, '',  'Hello John') value2 from o_test;

VALUE2
----------
111111
Hello John
222222

SQL> select coalesce(value3,'', to_date( '2010-01-01','YYYY-MM-DD')) value3 from o_test;
select coalesce(value3,'', to_date( '2010-01-01','YYYY-MM-DD')) value3 from o_test
                       *
Error in Line 1:
ORA-00932:  Data types are not consistent:  The type should be DATE, but CHAR is returned

SQL> select coalesce(value3, null, to_date( '2010-01-01','YYYY-MM-DD')) value3 from o_test;

VALUE3
--------------
05-Aug-16
31-Jul-16
01-Jan-10


PostgreSQL NULL judgment function
postgres=# select * from p_test;
 value1 | value2 |       value3
--------+--------+---------------------
        | 11111  | 2010-01-01 00:00:00
      1 |        | 2010-01-01 00:00:00
      2 | 22222  |
(3 rows of record)

postgres=# select coalesce(value1, 'Hello') value1  from p_test;
Error:   Invalid integer type input syntax:  "Hello"
Row 1 select coalesce(value1, 'Hello') value1  from p_test;
                             ^
postgres=# select coalesce(value1, '10000') value1  from p_test;
 value1
--------
  10000
      1
      2
(3 rows of records)

postgres=# select coalesce(value2, null, 'Hello world') value2  from p_test;
   value2
-------------
 11111
 Hello world
 22222
(3 rows of records)

postgres=# select coalesce(value3, null, '2012-10-10') value3  from p_test;
       value3
---------------------
 2010-01-01 00:00:00
 2016-08-05 10:01:32
 2012-10-10 00:00:00
(3 rows of records)

postgres=# select coalesce(value3, null, '2012-10-A') value3  from p_test;
Error:   Invalid timestamp type input syntax:  "2012-10-A"
Line 1 select coalesce(value3, null, '2012-10-A') value3  from p_te...
                                   ^


2. Character string connection
2.1. Character string connector (||)
The character string connector (||) in Oracle and the character string connector (||) in PostgreSQL differ only in that:
1. When the connect argument contains null, in Oracle, the connect result of it is similar to an empty character string (''), but in PostgreSQL, the connect results of arguments containing null are all null.
2. When several arguments are all numbers, Oracle will automatically convert the number to character strings. This is related to the internal automatic type conversion in Oracle. In PostgreSQL, at least one of the several arguments should be a character string, otherwise it will report errors.
In data migration, A || B of Oracle can be converted into coalesce (A, ‘’) || coalesce (B, ‘’) of PostgreSQL.
Oracle character string connector ( || )
SQL> select 'abc' || 'def' value from dual;

VALUE
------
abcdef

SQL> select 123 || 456 value from dual;

VALUE
------
123456

SQL> select null || 456 value from dual;

VAL
---
456

SQL> select null || 'abcdef'  value from dual;

VALUE
------
abcdef

SQL> select length(null || null) value from dual;

     VALUE
----------


PostgreSQL character string connector ( || )
postgres=# select 'abc' || 'def' as value;
 value
--------
 abcdef
(1 row of records)

postgres=# select 123 || 456 as  value;
Error:   Operator does not exist:  integer || integer
Line 1 select 123 || 456 as  value;
                ^
Note:   If there are no matched operators for the designated name and argument type, you may need to add a specific type conversion.
postgres=# select 123||'456' as value;
 value
--------
 123456
(1 row of records)

postgres=#  select null || 456 as  value ;
 value
-------

(1 row of records)

postgres=# select null || 'abcdef'  as value;
 value
-------

(1 row of records)

postgres=# select length(null || null) as value ;
 value
-------

(1 row of records)


2.2. Character string connector function concat
The concat function of Oracle is similar to the character string connector(||), but it can only connect two arguments. The arguments are mainly character strings or the arguments can be converted to character strings.
PostgreSQL also supports this method.
It is worth noting that concat in Oracle will return null if both of the two arguments are null. But in PostgreSQL, if both arguments are null, the result will be an empty character string (''), because the concat in PostgreSQL applies coalesce(null, ‘’) on internal arguments.
Oracle concat
SQL> select concat('abc','def') from dual;

CONCAT
------
abcdef

SQL> select concat(123, 456) from dual;

CONCAT
------
123456

SQL> select concat(null, 456) value from dual;

VAL
---
456

SQL> select concat(null, 'abc') value from dual;

VAL
---
abc

SQL> select concat(null, null) value from dual;

V
-


SQL> select length(concat(null, null)) value from dual;

     VALUE
----------

SQL> select * from o_test;

    VALUE1 VALUE2     VALUE3
---------- ---------- --------------
           111111     05-Aug-16
         1            31-Jul-16
         2 222222

SQL> select concat(value3, value2) from o_test;

CONCAT(VALUE3,VALUE2)
------------------------
05-Aug-16111111
31-Jul-16
222222


PostgreSQL character string connector function
postgres=# select concat('abc','def');
 concat
--------
 abcdef
(1 row of records)

postgres=# select concat(123, 456);
 concat
--------
 123456
(1 row of records)

postgres=# select concat(null, 456) as value;
 value
-------
 456
(1 row of records)


postgres=# select concat(null, 'abc') as value;
 value
-------
 abc
(1 row of records)

postgres=# select concat(null, null) as value;
 value
-------

(1 row of records)

postgres=# select length(concat(null, null)) as value;
 value
-------
     0
(1 row of records)

postgres=# select * from p_test;
 value1 | value2 |       value3
--------+--------+---------------------
        | 11111  | 2010-01-01 00:00:00
      1 |        | 2016-08-05 10:01:32
      2 | 22222  |
(3 rows of records)

postgres=# select concat(value3, value2)   as value from p_test;
          value
--------------------------
 2010-01-01 00:00:0011111
 2016-08-05 10:01:32
 22222
(3 rows of records)
Guest