Thread: 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 >