Combining text fields

Поиск
Список
Период
Сортировка
Искать

Combining text fields

От:
Doug Silver <dsilver@urchin.com>
Дата:
I just want to confirm that this is the correct behavior for the return value 
when combining text fields together.  If one of the fields is NULL, then the 
return value of field1+field2 also becomes NULL.  If this is correct, is the 
proper way to get around this to use a default value of '' for such fields?

TIA

-doug

# create table test2 (street1 varchar(10),street2 varchar(10),id serial);
NOTICE:  CREATE TABLE will create implicit sequence 'test2_id_seq' for SERIAL 
column 'test2.id'
CREATE TABLE
test=# insert into test2 (street1) values('123 main');
INSERT 832047 1
test=# select street1||' '||street2||'|' as street1,'|'||street2||'|' from 
test2;
 street1 | ?column?
---------+----------
         |
(1 row)

test=# select street1||' '||street2||'|' as 
street1,'|'||street1||'|,'|'||street2||'|' from test2;
test'#
test=# select street1||' '||street2||'|' as 
street1,'|'||street1||'|','|'||street2||'|' from test2;
 street1 |  ?column?  | ?column?
---------+------------+----------
         | |123 main| |

Re: Combining text fields

От:
Josh Berkus <josh@agliodbs.com>
Дата:
Doug,

> I just want to confirm that this is the correct behavior for the return 
value 
> when combining text fields together.  If one of the fields is NULL, then the 
> return value of field1+field2 also becomes NULL.  If this is correct, is the 
> proper way to get around this to use a default value of '' for such fields?

Use "COALESCE", e.g.:

SELECT street1 || COALESCE(street2, '') || COALESCE(street3, '')

-- 
-Josh Berkus
 Aglio Database Solutions
 San Francisco

Re: Combining text fields

От:
Nabil Sayegh <postgresql@e-trolley.de>
Дата:
Doug Silver wrote:
> I just want to confirm that this is the correct behavior for the return value 
> when combining text fields together.  If one of the fields is NULL, then the 
> return value of field1+field2 also becomes NULL.  If this is correct, is the 
> proper way to get around this to use a default value of '' for such fields?

This is correct behaviour (every comparison with NULL returns NULL)

http://developer.postgresql.org/docs/postgres/functions-comparison.html
-------------------------------8<----------------------------------------
Some applications may (incorrectly) require that expression = NULL 
returns true if expression evaluates to the null value. To support these 
applications, the run-time option transform_null_equals can be turned on 
(e.g., SET transform_null_equals TO ON;). PostgreSQL will then convert x 
= NULL clauses to x IS NULL. This was the default behavior in releases 
6.5 through 7.1.
-------------------------------8<----------------------------------------

HTH
-- 
  e-Trolley Sayegh & John, Nabil Sayegh
  Tel.: 0700 etrolley /// 0700 38765539
  Fax.: +49 69 8299381-8
  PGP : http://www.e-trolley.de
FAQ