Re: [PERFORM] <empty string> Vs NULL

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 




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

[Index of Archives]     [KVM ARM]     [KVM ia64]     [KVM ppc]     [Virtualization Tools]     [Spice Development]     [Libvirt]     [Libvirt Users]     [Linux USB Devel]     [Linux Audio Users]     [Yosemite Questions]     [Linux Kernel]     [Linux SCSI]     [XFree86]

  Powered by Linux