![]() 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 |