Re: [PERFORM] <empty string> Vs NULL

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(at)nuxeo(dot)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(dot)bn1(at)gmail(dot)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(dot)Mamin(at)intershop(dot)de> wrote:
> >>
> >>
> >> >>>Hi
> >> >>>
> >> >>>2015-02-09 12:22 GMT+01:00 sridhar bamandlapally
> >> >>> <sridhar(dot)bn1(at)gmail(dot)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
>