One small/big differences between Oracle and any other databases is meaning of empty string. There are lot of situation, when Oracle use empty string as NULL, and NULL as empty string. I don't know any other database, that does it.
Orafce has native type (not domain type)
varchar2
and nvarchar2
. Then it is possible to define own operators. I implemented ||
concat operator as null safe for these types. So now it is possible to write:postgres=# select null || 'xxx'::varchar2 || null; ┌──────────┐ │ ?column? │ ╞══════════╡ │ xxx │ └──────────┘ (1 row)
When you port some application from Oracle to Postgres, then is good to disallow empty strings in Postgres. One possible solution is using generic C trigger function
replace_empty_string()
. This trigger function can check any text type field in stored rows and can replace empty strings by NULLs. Sure, you should to fix any check like colname = '' or colname <> ''
in your application, and you should to use just only colname IS [NOT] NULL
. Then the code will be same on Oracle and PostgreSQL too, and you can use automatic translation by ora2pg. Orafce || (n)varchar2 operator automatically convert empty string to NULL.
No comments:
Post a Comment