|
>>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. 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 |
