![]() 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.Relational databases are getting bigger and more complex. It is worth noting that concat in Oracle will return null if both of the two arguments are null. The arguments are mainly character strings or the arguments can be converted to character strings. The concat function of Oracle is similar to the character string connector(||), but it can only connect two arguments. Character string connector function concat Postgres=# select length(null || null) as value Ģ.2. Postgres=# select null || 'abcdef' 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 'abc' || 'def' as value Įrror: Operator does not exist: integer || integer PostgreSQL character string connector ( || ) SQL> select length(null || null) value from dual SQL> select null || 'abcdef' value from dual SQL> select 'abc' || 'def' value from dual In data migration, A || B of Oracle can be converted into coalesce (A, ‘’) || coalesce (B, ‘’) of PostgreSQL. In PostgreSQL, at least one of the several arguments should be a character string, otherwise it will report errors. ![]() This is related to the internal automatic type conversion in Oracle. When several arguments are all numbers, Oracle will automatically convert the number to character strings. 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.Ģ. The character string connector (||) in Oracle and the character string connector (||) in PostgreSQL differ only in that:ġ. Line 1 select coalesce(value3, null, '2012-10-A') value3 from p_te. Postgres=# select coalesce(value3, null, '2012-10-A') value3 from p_test Įrror: Invalid timestamp type input syntax: "2012-10-A" Postgres=# select coalesce(value3, null, '') value3 from p_test Postgres=# select coalesce(value2, null, 'Hello world') value2 from p_test Postgres=# select coalesce(value1, '10000') value1 from p_test Row 1 select coalesce(value1, 'Hello') value1 from p_test Postgres=# select coalesce(value1, 'Hello') value1 from p_test Įrror: Invalid integer type input syntax: "Hello" SQL> select coalesce(value3, null, to_date( '','YYYY-MM-DD')) value3 from o_test ORA-00932: Data types are not consistent: The type should be DATE, but CHAR is returned Select coalesce(value3,'', to_date( '','YYYY-MM-DD')) value3 from o_test SQL> select coalesce(value3,'', to_date( '','YYYY-MM-DD')) value3 from o_test SQL> select coalesce(value2, '', 'Hello John') value2 from o_test SQL> select coalesce(value1, 10000) value1 from o_test ORA-00932: Data types are not consistent: The type should be NUMBER, but CHAR is returned Select coalesce(value1, '10000') value1 from o_test SQL> select coalesce(value1, '10000') value1 from o_test SQL> select nvl(value3, to_date( '','YYYY-MM-DD')) value3 from o_test ORA-01861: Text and format character strings do not match Select nvl(value3, '') value3 from o_test SQL> select nvl(value3, '') value3 from o_test SQL> select nvl(value2, 'Hello') value2 from o_test SQL> select nvl(value1, '10000') value1 from o_test Select nvl(value1, 'Hello') value1 from o_test SQL> select nvl(value1, 'Hello') value1 from o_test The arguments have to be of the same type, or can be automatically converted to the same type. ![]() You can utilize coalesce to convert nvl and coalesce functions of Oracle. The usage is the same with that in Oracle. PostgreSQL does not support nvl functions, but it supports coalesce functions. When the arguments must be of the same type, no automatic conversion will be performed. The coalesce arguments can be more than one, and the first non-NULL argument will be returned. Otherwise explicit conversion is required. nvl(A, B) returns A if it judges A is not NULL, otherwise it returns B. The NULL judgment functions in Oracle are nvl(A,B) and coalesce. If there are deficiencies in my articles, your advice is highly welcomed. I wrote some articles on SQL and database object conversion based on my understandings and tests. 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. As PostgreSQL application expands, there are more and more requests for data migration from Oracle to PostgreSQL databases. 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.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |