Thread: Vs NULL
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
Please,
Thanks
Sridhar BN
Hi
2015-02-09 12:22 GMT+01:00 sridhar bamandlapally <sridhar.bn1@gmail.com>:
Hi AllWe are testing our Oracle compatible business applications on PostgreSQL database,the issue we are facing is <empty string> Vs NULLIn Oracle '' (<empty string>) and NULL are treated as NULLbut, in PostgreSQL '' <empty string> not treated as NULLI 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
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.
Please,ThanksSridhar BN
Its been a while since I really worked with Postgres, but could you write a trigger to convert empty string to null on save? You'd have to carefully apply it everywhere but it'd get you the searching for null finds empty. If that is all you do the you've got it.
Essentially, there isn't a switch for it but you can do it with some mechanisms.
Nik
On Feb 9, 2015 6:54 AM, "Pavel Stehule" <pavel.stehule@gmail.com> wrote:
Hi2015-02-09 12:22 GMT+01:00 sridhar bamandlapally <sridhar.bn1@gmail.com>:Hi AllWe are testing our Oracle compatible business applications on PostgreSQL database,the issue we are facing is <empty string> Vs NULLIn Oracle '' (<empty string>) and NULL are treated as NULLbut, in PostgreSQL '' <empty string> not treated as NULLI need some implicit way in PostgreSQL where ''<empty string> can be treated as NULLIt is not possible in PostgreSQL. PostgreSQL respects ANSI SQL standard - Oracle not.Regards
Pavelp.s. theoretically you can overwrite a type operators to support Oracle behave, but you should not be sure about unexpected negative side effects.Please,ThanksSridhar BN
>>Hi
>>
>>2015-02-09 12:22 GMT+01:00 sridhar bamandlapally <sridhar.bn1@gmail.com>:
>>
>> 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.
ALTER TABLE tablename ADD CONSTRAINT tablename_not_empty_ck
CHECK (false= (colname1 IS NULL OR colname2 IS NULL OR colname3 IS NULL ...) 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
the issue we are facing is <empty string> Vs NULLIn Oracle '' (<empty string>) and NULL are treated as NULLbut, in PostgreSQL '' <empty string> not treated as NULLI need some implicit way in PostgreSQL where ''<empty string> can be treated as NULL
The Right Thing to do is to fix your application, and don't use broken DBMSes: NULL should not denote anything except "this value is not set". If you count an empty string as null, how do you represent the empty string?Oracle's own documentation suggests that developers should not rely on this behaviour since it may change in the future.
So Do The Right Thing now, and you won't get bitten later.
Geoff
Oracle's own documentation suggests that developers should not rely on this behaviour since it may change in the future.
Just in case you're looking for it:Note:Oracle Database currently treats a character value with a length of zero as null. However, this may not continue to be true in future releases, and Oracle recommends that you do not treat empty strings the same as nulls.
Geoff
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@intershop.de> wrote:
>>>Hi
>>>
>>>2015-02-09 12:22 GMT+01:00 sridhar bamandlapally <sridhar.bn1@gmail.com>:
>>>
>>> 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
Hi, Please take this to another list, this has little to do with PostgreSQL admin or performance. Florent On Tue, Feb 10, 2015 at 4:53 AM, sridhar bamandlapally <sridhar.bn1@gmail.com> wrote: > 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@intershop.de> wrote: >> >> >> >>>Hi >> >>> >> >>>2015-02-09 12:22 GMT+01:00 sridhar bamandlapally >> >>> <sridhar.bn1@gmail.com>: >> >>> >> >>> 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 > > -- Florent Guillaume, Director of R&D, Nuxeo Open Source Content Management Platform for Business Apps http://www.nuxeo.com http://community.nuxeo.com