pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedconcatenation with a null column (using ||) nulls the result?
5+ messages / 5 participants
[nested] [flat]
* concatenation with a null column (using ||) nulls the result?
@ 2006-04-10 21:36 Neil Harkins <nharkins@well.com>
0 siblings, 3 replies; 5+ messages in thread
From: Neil Harkins @ 2006-04-10 21:36 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: concatenation with a null column (using ||) nulls the result?
@ 2006-04-10 21:47 Tom Lane <tgl@sss.pgh.pa.us>
parent: Neil Harkins <nharkins@well.com>
2 siblings, 0 replies; 5+ messages in thread
From: Tom Lane @ 2006-04-10 21:47 UTC (permalink / raw)
To: Neil Harkins <nharkins@well.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: concatenation with a null column (using ||) nulls the result?
@ 2006-04-10 21:55 Alvaro Herrera <alvherre@commandprompt.com>
parent: Neil Harkins <nharkins@well.com>
2 siblings, 1 reply; 5+ messages in thread
From: Alvaro Herrera @ 2006-04-10 21:55 UTC (permalink / raw)
To: Neil Harkins <nharkins@well.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: concatenation with a null column (using ||) nulls the result?
@ 2006-04-10 22:03 Terry Lee Tucker <terry@esc1.com>
parent: Alvaro Herrera <alvherre@commandprompt.com>
0 siblings, 0 replies; 5+ messages in thread
From: Terry Lee Tucker @ 2006-04-10 22:03 UTC (permalink / raw)
To: pgsql-sql
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...
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: concatenation with a null column (using ||) nulls the
@ 2006-04-10 22:52 Ross Johnson <ross.johnson@homemail.com.au>
parent: Neil Harkins <nharkins@well.com>
2 siblings, 0 replies; 5+ messages in thread
From: Ross Johnson @ 2006-04-10 22:52 UTC (permalink / raw)
To: Neil Harkins <nharkins@well.com>; +Cc: pgsql-sql
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
>
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2006-04-10 22:52 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-04-10 21:36 concatenation with a null column (using ||) nulls the result? Neil Harkins <nharkins@well.com>
2006-04-10 21:47 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-04-10 21:55 ` Alvaro Herrera <alvherre@commandprompt.com>
2006-04-10 22:03 ` Terry Lee Tucker <terry@esc1.com>
2006-04-10 22:52 ` Re: concatenation with a null column (using ||) nulls the Ross Johnson <ross.johnson@homemail.com.au>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox