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.ma...@intershop.de> wrote: > > >>>Hi > >>> > >>>2015-02-09 12:22 GMT+01:00 sridhar bamandlapally <sridhar....@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 >
