coalesce for null AND empty strings

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:

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

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

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:

* 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