Re: concatenation with a null column (using ||) nulls the

On Mon, 2006-04-10 at 14:36 -0700, Neil Harkins wrote:
> inventory=> SELECT cabinets_name, cabinets_description
> FROM cabinets WHERE cabinets_datacenters = 2;
> cabinets_name | cabinets_description
> ---------------+----------------------
> 548-4th-Cab2 |
> 548-4th-RR1 |
> 548-4th-RR2 |
> 548-4th-Cab1 |
> (4 rows)
>
> inventory=> SELECT cabinets_name || ' - ' || cabinets_description AS concat
> FROM cabinets WHERE cabinets_datacenters = 2;
> concat
> -----------------
>
>
>
> 548-4th-Cab1 -
> (4 rows)
>
> Note: The cabinets_description for the "548-4th-Cab1" row is " ",
> not NULL, hence it being displayed. Is this standard SQL behavior?
>

I don't know if this is the "correct SQL" answer, however, in the past,
I've used the COALESCE() function to handle this situation.

<quote>
COALESCE(value [, ...])

The COALESCE function returns the first of its arguments that is not
null. Null is returned only if all arguments are null. This is often
useful to substitute a default value for null values when data is
retrieved for display, for example:
SELECT COALESCE(description, short_description, '(none)') ...

Like a CASE expression, COALESCE will not evaluate arguments that are
not needed to determine the result; that is, arguments to the right of
the first non-null argument are not evaluated.
</quote>

Regards.

> Client is from rpm: postgresql-8.0.7-1.FC4.1
> Server is from rpm: postgresql-server-8.0.7-1.FC4.1
>
> -neil
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
> http://archives.postgresql.org
>