Thread: coalesce for null AND empty strings
Hi! it would be great to have a coalesce2 function which treats empty strings as null values. as far as I have seen, there are a lot of comments and coding solutions about this, but none is an "easy" one and all make the code a bit more complicated and more difficult to maintain. I have created this function. It's similar to nullif, but takes only ONE argument create or replace function "empty2null"(text_i varchar) returns varchar as $$ declare text_p varchar; begin if text_i = '' then text_p := null; else text_p := text_i; end if; return text_p; end; $$ LANGUAGE plpgsql; -- cu Ferdinand
Ferdinand Gassauer wrote: > Hi! > > it would be great to have a coalesce2 function which treats empty strings as > null values. Why? What is the use-case for this? > as far as I have seen, there are a lot of comments and coding solutions about > this, but none is an "easy" one and all make the code a bit more complicated > and more difficult to maintain. > > I have created this function. > It's similar to nullif, but takes only ONE argument > > create or replace function "empty2null"(text_i varchar) > returns varchar as $$ > declare > text_p varchar; > begin > if text_i = '' > then text_p := null; > else text_p := text_i; > end if; > return text_p; > end; > $$ LANGUAGE plpgsql; or even shorter: CREATE OR REPLACE FUNCTION empty2null(varchar) RETURNS varchar AS $$ SELECT CASE WHEN $1 = '' THEN NULL ELSE $1 END; $$ LANGUAGE SQL; -- Richard Huxton Archonet Ltd
Don't forget to cc: the list! Ferdinand Gassauer wrote: > Am Freitag, 30. März 2007 schrieben Sie: >> Ferdinand Gassauer wrote: >>> Hi! >>> >>> it would be great to have a coalesce2 function which treats empty strings >>> as null values. >> Why? What is the use-case for this? >> >>> as far as I have seen, there are a lot of comments and coding solutions >>> about this, but none is an "easy" one and all make the code a bit more >>> complicated and more difficult to maintain. >>> >>> I have created this function. >>> It's similar to nullif, but takes only ONE argument >>> >>> create or replace function "empty2null"(text_i varchar) >>> returns varchar as $$ >>> declare >>> text_p varchar; >>> begin >>> if text_i = '' >>> then text_p := null; >>> else text_p := text_i; >>> end if; >>> return text_p; >>> end; >>> $$ LANGUAGE plpgsql; >> or even shorter: >> >> CREATE OR REPLACE FUNCTION empty2null(varchar) RETURNS varchar AS $$ >> SELECT CASE WHEN $1 = '' THEN NULL ELSE $1 END; >> $$ LANGUAGE SQL; > > OK this shortens the function, but does not help to "solve" the coalesce > problem > coalecse(empty2null(var1),empty2null(var2),....empty2null(var-n)) > instead of > coalecse2(var1,var2,...var-n) > > where the empty2null is doing it's job "inside" the coalesce. Well, you can always write the four or five variations you want: CREATE OR REPLACE FUNCTION coalesce_and_blank(varchar) ... CREATE OR REPLACE FUNCTION coalesce_and_blank(varchar,varchar) ... CREATE OR REPLACE FUNCTION coalesce_and_blank(varchar,varchar,varchar) ... etc. > BTW I use now > if rtrim(text_i,' ') = ... > to remove all blanks > > Badly enough null, empty strings and strings with blanks are not easy to > distinguish and in most apps it is even impossible for the user, so this case > has to be addressed somewhere. Well, yes. > a) make the application to handle this Exactly. If you're going to allow NULLs to the user interface you'll need some way to display them. If it's an unformatted text-field (e.g. "description" or "name" you probably want NOT NULL. > b) write a trigger on every table char not null field Yes - if you want to trim leading/trailing spaces automatically. The other thing you can do is define checks to make sure the first/last character are not a space in the database, and the automatic trimming in the application. > c) have a confortable function, where needed. that's the idea I'm still not sure where these nulls are coming from, if your application isn't generating them. -- Richard Huxton Archonet Ltd
Ferdinand Gassauer wrote: > Hi! > > it would be great to have a coalesce2 function which treats empty strings as > null values. I think COALESCE(NULLIF(value1, ''), value2) does what you want. You could wrap that in a new function coalesce2 if you like. -- Alban Hertroys alban@magproductions.nl magproductions b.v. T: ++31(0)534346874 F: ++31(0)534346876 M: I: www.magproductions.nl A: Postbus 416 7500 AK Enschede // Integrate Your World //
Ferdinand Gassauer wrote: > On Friday 30 March 2007 10:19:35 Richard Huxton wrote: >> Don't forget to cc: the list! > > snip >>> OK this shortens the function, but does not help to "solve" the coalesce >>> problem >>> coalecse(empty2null(var1),empty2null(var2),....empty2null(var-n)) >>> instead of >>> coalecse2(var1,var2,...var-n) >>> >>> where the empty2null is doing it's job "inside" the coalesce. >> Well, you can always write the four or five variations you want: >> CREATE OR REPLACE FUNCTION coalesce_and_blank(varchar) ... >> CREATE OR REPLACE FUNCTION coalesce_and_blank(varchar,varchar) ... >> CREATE OR REPLACE FUNCTION coalesce_and_blank(varchar,varchar,varchar) ... > OK - this is a solution I didn't think off , because other system do not allow > this sort of "overloading". > > Nevertheless I think > * coalesce takes n arguments, which would need the user to write n functions > to fully cover the functionality - even though I think it's seldom to have > more than 4-5 arguments. > * looking through Google it IS a concern for many others and thus this > function should be distributed as default. Hmm - I see it coming up occasionally, but usually where people are misusing NULLs. What you might want to do is tidy up a function and post it to the list for the record. Or, you could start a project on pgfoundry to share it. -- Richard Huxton Archonet Ltd
Ferdinand Gassauer wrote: > it would be great to have a coalesce2 function which treats empty strings as > null values. I disagree, it would be the opposite of "great". "" is a /known/ value and not in the same semantic space as NULL at all. If you really feel you need such a function, though, why don't you write one? I recommend sticking with the SQL semantics instead of trying to change them. -- Lew
Ferdinand Gassauer wrote: >> * looking through Google it IS a concern for many others and thus this >> function should be distributed as default. The mere fact that something is requested does not imply that the request should be accepted. I'm sure there's a name for this fallacy, but I can't think of it just now. This is especially true when the common request stems from common ignorance. Richard Huxton wrote: > Hmm - I see it coming up occasionally, but usually where people are > misusing NULLs. This is why the request should be rejected. NULL has a specific semantic, and it is most emphatically not the same as an empty string. It is not wise to mess with the fundamental mathematics of SQL. Get used to the fact that NULL is not empty string; embrace the fact and learn to love it. -- Lew
On Friday 30 March 2007 10:19:35 Richard Huxton wrote: > Don't forget to cc: the list! snip > > OK this shortens the function, but does not help to "solve" the coalesce > > problem > > coalecse(empty2null(var1),empty2null(var2),....empty2null(var-n)) > > instead of > > coalecse2(var1,var2,...var-n) > > > > where the empty2null is doing it's job "inside" the coalesce. > > Well, you can always write the four or five variations you want: > CREATE OR REPLACE FUNCTION coalesce_and_blank(varchar) ... > CREATE OR REPLACE FUNCTION coalesce_and_blank(varchar,varchar) ... > CREATE OR REPLACE FUNCTION coalesce_and_blank(varchar,varchar,varchar) ... OK - this is a solution I didn't think off , because other system do not allow this sort of "overloading". Nevertheless I think * coalesce takes n arguments, which would need the user to write n functions to fully cover the functionality - even though I think it's seldom to have more than 4-5 arguments. * looking through Google it IS a concern for many others and thus this function should be distributed as default. -- cu ferdinand