From: | sridhar bamandlapally <sridhar(dot)bn1(at)gmail(dot)com> |
---|---|
To: | Marc Mamin <M(dot)Mamin(at)intershop(dot)de> |
Cc: | pgsql-admin <pgsql-admin(at)postgresql(dot)org>, pgsql-performance <pgsql-performance(at)postgresql(dot)org> |
Subject: | Re: [PERFORM] <empty string> Vs NULL |
Date: | 2015-02-10 03:53:35 |
Message-ID: | CAGuFTBXaMmPuY8H1pFGvJWA=tUiTM0g92=_a8eHA=ACQmjEikA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-admin 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(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
>
From | Date | Subject | |
---|---|---|---|
Next Message | Scott Ribe | 2015-02-10 04:50:42 | Re: [PERFORM] <empty string> Vs NULL |
Previous Message | Bruce Momjian | 2015-02-09 19:25:03 | Re: Lost statistics after restore database |
From | Date | Subject | |
---|---|---|---|
Next Message | Scott Ribe | 2015-02-10 04:50:42 | Re: [PERFORM] <empty string> Vs NULL |
Previous Message | Josh Berkus | 2015-02-09 22:04:38 | Re: Poor performance when deleting from entity-attribute-value type master-table |