Обсуждение: concatenation with a null column (using ||) nulls the result?

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? 

Client is from rpm: postgresql-8.0.7-1.FC4.1
Server is from rpm: postgresql-server-8.0.7-1.FC4.1

-neil

Neil Harkins <nharkins@well.com> writes:
> Is this standard SQL behavior? 

Yes.  SQL92 6.13 <string value expression>, general rule 2:
        2) If <concatenation> is specified, then let S1 and S2 be the re-           sult of the <character value
expression>and <character factor>,           respectively.
 
           a) If either S1 or S2 is the null value, then the result of the             <concatenation> is the null
value.
    ...
        regards, tom lane

Neil Harkins wrote:

> Note: The cabinets_description for the "548-4th-Cab1" row is " ", 
> not NULL, hence it being displayed. Is this standard SQL behavior? 

Yes; something || NULL yields NULL.  If you want NULL to behave as ""
for the purpose of the concatenation, try

SELECT cabinets_name || ' - ' || COALESCE(cabinets_description, '') AS concat 
FROM cabinets WHERE cabinets_datacenters = 2;

I'm assuming cabinets_name is NOT NULL, so it doesn't need COALESCE.

-- 
Alvaro Herrera                                http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

On Monday 10 April 2006 05:55 pm, Alvaro Herrera saith:
> Neil Harkins wrote:
> > Note: The cabinets_description for the "548-4th-Cab1" row is " ",
> > not NULL, hence it being displayed. Is this standard SQL behavior?
>
> Yes; something || NULL yields NULL.  If you want NULL to behave as ""
> for the purpose of the concatenation, try
>
> SELECT cabinets_name || ' - ' || COALESCE(cabinets_description, '') AS
> concat FROM cabinets WHERE cabinets_datacenters = 2;
>
> I'm assuming cabinets_name is NOT NULL, so it doesn't need COALESCE.
>
> --

Good to know. Thanks for the input...

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
>