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.bn1@xxxxxxxxx> 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.Mamin@xxxxxxxxxxxx> wrote: >> >> >> >>>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 > > -- Florent Guillaume, Director of R&D, Nuxeo Open Source Content Management Platform for Business Apps http://www.nuxeo.com http://community.nuxeo.com -- Sent via pgsql-admin mailing list (pgsql-admin@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
