Re: [PERFORM] <empty string> Vs NULL

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

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