pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feed<empty string> Vs NULL
13+ messages / 8 participants
[nested] [flat]
* <empty string> Vs NULL
@ 2015-02-09 11:22 sridhar bamandlapally <sridhar.bn1@gmail.com>
0 siblings, 2 replies; 13+ messages in thread
From: sridhar bamandlapally @ 2015-02-09 11:22 UTC (permalink / raw)
To: pgsql-performance
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
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: [PERFORM] <empty string> Vs NULL
@ 2015-02-09 11:53 Pavel Stehule <pavel.stehule@gmail.com>
parent: sridhar bamandlapally <sridhar.bn1@gmail.com>
1 sibling, 2 replies; 13+ messages in thread
From: Pavel Stehule @ 2015-02-09 11:53 UTC (permalink / raw)
To: sridhar bamandlapally <sridhar.bn1@gmail.com>; +Cc: pgsql-admin <pgsql-admin@postgresql.org>; pgsql-performance
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.
>
> Please,
>
> Thanks
> Sridhar BN
>
>
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: <empty string> Vs NULL
@ 2015-02-09 12:36 Nikolas Everett <nik9000@gmail.com>
parent: Pavel Stehule <pavel.stehule@gmail.com>
1 sibling, 0 replies; 13+ messages in thread
From: Nikolas Everett @ 2015-02-09 12:36 UTC (permalink / raw)
To: Pavel Stehule <pavel.stehule@gmail.com>; +Cc: pgsql-admin <pgsql-admin@postgresql.org>; sridhar bamandlapally <sridhar.bn1@gmail.com>; pgsql-performance
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:
> 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.
>
>
>
>>
>> Please,
>>
>> Thanks
>> Sridhar BN
>>
>>
>
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: [PERFORM] <empty string> Vs NULL
@ 2015-02-09 12:42 Marc Mamin <M.Mamin@intershop.de>
parent: Pavel Stehule <pavel.stehule@gmail.com>
1 sibling, 1 reply; 13+ messages in thread
From: Marc Mamin @ 2015-02-09 12:42 UTC (permalink / raw)
To: sridhar bamandlapally <sridhar.bn1@gmail.com>; +Cc: pgsql-admin <pgsql-admin@postgresql.org>; pgsql-performance
>>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
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: [ADMIN] <empty string> Vs NULL
@ 2015-02-09 12:48 Geoff Winkless <pgsqladmin@geoff.dj>
parent: sridhar bamandlapally <sridhar.bn1@gmail.com>
1 sibling, 1 reply; 13+ messages in thread
From: Geoff Winkless @ 2015-02-09 12:48 UTC (permalink / raw)
To: sridhar bamandlapally <sridhar.bn1@gmail.com>; +Cc: pgsql-admin@postgresql.org <pgsql-admin@postgresql.org>; pgsql-performance
On 9 February 2015 at 11:22, sridhar bamandlapally <sridhar.bn1@gmail.com>
wrote:
> 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
>
>
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
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: <empty string> Vs NULL
@ 2015-02-09 12:57 Geoff Winkless <pgsqladmin@geoff.dj>
parent: Geoff Winkless <pgsqladmin@geoff.dj>
0 siblings, 0 replies; 13+ messages in thread
From: Geoff Winkless @ 2015-02-09 12:57 UTC (permalink / raw)
To: sridhar bamandlapally <sridhar.bn1@gmail.com>; +Cc: pgsql-admin@postgresql.org <pgsql-admin@postgresql.org>; pgsql-performance
On 9 February 2015 at 12:48, Geoff Winkless <pgsqladmin@geoff.dj> wrote:
> 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:
http://docs.oracle.com/database/121/SQLRF/sql_elements005.htm#SQLRF30037
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
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: [PERFORM] <empty string> Vs NULL
@ 2015-02-09 13:02 Marc Mamin <M.Mamin@intershop.de>
parent: Marc Mamin <M.Mamin@intershop.de>
0 siblings, 1 reply; 13+ messages in thread
From: Marc Mamin @ 2015-02-09 13:02 UTC (permalink / raw)
To: sridhar bamandlapally <sridhar.bn1@gmail.com>; +Cc: pgsql-admin <pgsql-admin@postgresql.org>
>>>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
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: [PERFORM] <empty string> Vs NULL
@ 2015-02-10 03:53 sridhar bamandlapally <sridhar.bn1@gmail.com>
parent: Marc Mamin <M.Mamin@intershop.de>
0 siblings, 3 replies; 13+ messages in thread
From: sridhar bamandlapally @ 2015-02-10 03:53 UTC (permalink / raw)
To: Marc Mamin <M.Mamin@intershop.de>; +Cc: pgsql-admin <pgsql-admin@postgresql.org>; pgsql-performance
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
>
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: [PERFORM] <empty string> Vs NULL
@ 2015-02-10 04:50 Scott Ribe <scott_ribe@elevated-dev.com>
parent: sridhar bamandlapally <sridhar.bn1@gmail.com>
2 siblings, 0 replies; 13+ messages in thread
From: Scott Ribe @ 2015-02-10 04:50 UTC (permalink / raw)
To: sridhar bamandlapally <sridhar.bn1@gmail.com>; +Cc: pgsql-admin <pgsql-admin@postgresql.org>
On Feb 9, 2015, at 8:53 PM, sridhar bamandlapally <sridhar.bn1@gmail.com> wrote:
>
> the issue is, while DML its empty string and while SELECT its comparing with NULL
The issue is, empty string is NOT the same as null, and expecting select for null to match empty strings is a bug, which you need to fix.
--
Scott Ribe
scott_ribe@elevated-dev.com
http://www.elevated-dev.com/
(303) 722-0567 voice
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: [PERFORM] <empty string> Vs NULL
@ 2015-02-10 05:02 David G Johnston <david.g.johnston@gmail.com>
parent: sridhar bamandlapally <sridhar.bn1@gmail.com>
2 siblings, 0 replies; 13+ messages in thread
From: David G Johnston @ 2015-02-10 05:02 UTC (permalink / raw)
To: pgsql-admin@postgresql.org
sridhar bamandlapally 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
If this is the extent of your problem then you can add table triggers to
change the empty-string input so that the result of the insert/update is
NULL. Then all of your selects can use IS NULL for their comparisons just
like they do now.
That is as "implicit" as you are going to get without actually fixing the
underlying problem.
David J.
--
View this message in context: http://postgresql.nabble.com/empty-string-Vs-NULL-tp5837188p5837308.html
Sent from the PostgreSQL - admin mailing list archive at Nabble.com.
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: [PERFORM] <empty string> Vs NULL
@ 2015-02-10 10:07 Florent Guillaume <fg@nuxeo.com>
parent: sridhar bamandlapally <sridhar.bn1@gmail.com>
2 siblings, 1 reply; 13+ messages in thread
From: Florent Guillaume @ 2015-02-10 10:07 UTC (permalink / raw)
To: sridhar bamandlapally <sridhar.bn1@gmail.com>; +Cc: pgsql-admin <pgsql-admin@postgresql.org>; pgsql-performance
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
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: [PERFORM] <empty string> Vs NULL
@ 2015-02-10 10:40 sridhar bamandlapally <sridhar.bn1@gmail.com>
parent: Florent Guillaume <fg@nuxeo.com>
0 siblings, 1 reply; 13+ messages in thread
From: sridhar bamandlapally @ 2015-02-10 10:40 UTC (permalink / raw)
To: Florent Guillaume <fg@nuxeo.com>; +Cc: pgsql-admin <pgsql-admin@postgresql.org>
The first contact of database migration/issues is DBA (admin),
accept performance is not required
Thanks
Sridhar BN
On Tue, Feb 10, 2015 at 3:37 PM, Florent Guillaume <fg@nuxeo.com> wrote:
> 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
>
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: [PERFORM] <empty string> Vs NULL
@ 2015-02-10 14:32 Scott Ribe <scott_ribe@elevated-dev.com>
parent: sridhar bamandlapally <sridhar.bn1@gmail.com>
0 siblings, 0 replies; 13+ messages in thread
From: Scott Ribe @ 2015-02-10 14:32 UTC (permalink / raw)
To: sridhar bamandlapally <sridhar.bn1@gmail.com>; +Cc: pgsql-admin <pgsql-admin@postgresql.org>
On Feb 10, 2015, at 3:40 AM, sridhar bamandlapally <sridhar.bn1@gmail.com> wrote:
>
> The first contact of database migration/issues is DBA (admin),
This is a SQL usage issue, not a db admin issue, and so is most appropriate for the pgsql-general list. (Anyway, your question has been answered 4 times now.)
--
Scott Ribe
scott_ribe@elevated-dev.com
http://www.elevated-dev.com/
(303) 722-0567 voice
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 13+ messages in thread
end of thread, other threads:[~2015-02-10 14:32 UTC | newest]
Thread overview: 13+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2015-02-09 11:22 <empty string> Vs NULL sridhar bamandlapally <sridhar.bn1@gmail.com>
2015-02-09 11:53 ` Pavel Stehule <pavel.stehule@gmail.com>
2015-02-09 12:36 ` Nikolas Everett <nik9000@gmail.com>
2015-02-09 12:42 ` Marc Mamin <M.Mamin@intershop.de>
2015-02-09 13:02 ` Marc Mamin <M.Mamin@intershop.de>
2015-02-10 03:53 ` sridhar bamandlapally <sridhar.bn1@gmail.com>
2015-02-10 04:50 ` Scott Ribe <scott_ribe@elevated-dev.com>
2015-02-10 05:02 ` David G Johnston <david.g.johnston@gmail.com>
2015-02-10 10:07 ` Florent Guillaume <fg@nuxeo.com>
2015-02-10 10:40 ` sridhar bamandlapally <sridhar.bn1@gmail.com>
2015-02-10 14:32 ` Scott Ribe <scott_ribe@elevated-dev.com>
2015-02-09 12:48 ` Geoff Winkless <pgsqladmin@geoff.dj>
2015-02-09 12:57 ` Geoff Winkless <pgsqladmin@geoff.dj>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox