In application code is
while inserting/updating: INSERT/UPDATE into ... ( '' ) - which is empty string in PG, and in Oracle its NULL
while selecting: SELECT ... WHERE column IS NULL / NOT NULL
the issue is, while DML its empty string and while SELECT its comparing with NULL
On Mon, Feb 9, 2015 at 6:32 PM, Marc Mamin <M.Mamin@xxxxxxxxxxxx> wrote:
>>>Hi
>>>
>>>2015-02-09 12:22 GMT+01:00 sridhar bamandlapally <sridhar.bn1@xxxxxxxxx>:
>>>
>>> Hi All
>>>
>>> We are testing our Oracle compatible business applications on PostgreSQL database,
>>>
>>> the issue we are facing is <empty string> Vs NULL
>>>
>>> In Oracle '' (<empty string>) and NULL are treated as NULL
>>>
>>> but, in PostgreSQL '' <empty string> not treated as NULL
>>>
>>> I need some implicit way in PostgreSQL where ''<empty string> can be treated as NULL
>
>>It is not possible in PostgreSQL. PostgreSQL respects ANSI SQL standard - Oracle not.
>>
>>Regards
>>
>>Pavel
>>
>>p.s. theoretically you can overwrite a type operators to support Oracle behave, but you should not be sure about unexpected negative side effects.
>
>
>A clean way would be to disallow empty strings on the PG side.
>This is somewhat combersome depending on how dynamic your model is
>and add some last on your db though.
hmm, you could also consider disallowing NULLs, i.e. force empty strings.
this may result in a better compatibility although unwise from postgres point of view (see null storage in PG)
and neither way allow a compatibility out of the box:
Postgres ORACLE
'' IS NULL false true
NULL || 'foo' NULL 'foo'
as mention in another post, you need to check/fix your application.
>
>ALTER TABLE tablename ADD CONSTRAINT tablename_not_empty_ck
> CHECK (false= (colname1 IS NULL OR colname2 IS NULL OR colname3 IS NULL ...) IS NULL)
oops, this shold be
CHECK (false= (colname1 IS NULL OR colname2 IS NULL OR colname3 IS NULL ...))
>
>-- and to ensure compatibility with your app or migration:
>
>CREATE OR REPLACE FUNCTION tablename_setnull_trf()
> RETURNS trigger AS
>$BODY$
>BEGIN
>-- for all *string* columns
> NEW.colname1 = NULLIF (colname1,'');
> NEW.colname2 = NULLIF (colname2,'');
> NEW.colname3 = NULLIF (colname3,'');
>RETURN NEW;
>END;
>$BODY$
>
>CREATE TRIGGER tablename_setnull_tr
> BEFORE INSERT OR UPDATE
> ON tablename
> FOR EACH ROW
> EXECUTE PROCEDURE tablename_setnull_trf();
>
>You can query the pg catalog to generate all required statements.
>A possible issue is the order in which triggers are fired, when more than one exist for a given table:
>"If more than one trigger is defined for the same event on the same relation, the triggers will be fired in alphabetical order by trigger name"
>( http://www.postgresql.org/docs/9.3/static/trigger-definition.html )
>
>regards,
>
>Marc Mamin
