|
>>>Hi >>> >>>2015-02-09 12:22 GMT+01:00 sridhar bamandlapally <sridhar.bn1@xxxxxxxxx>: >>> >>> 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 |
