Обсуждение: Text concat problem

Hi:

Have this curious situation and would like some help from you:

Create an employee table:

CREATE  TABLE  employee(  id_employee SERIAL PRIMARY KEY,  sex CHAR(1) DEFAULT 'm' CHECK(sex = 'f' OR sex = 'm'),
start_dateDATE NOT NULL,  charge VARCHAR(50) NOT NULL,  last_name VARCHAR(50),  first_name VARCHAR(50) NOT NULL,  title
VARCHAR(10)NOT NULL
 
);

then fill it with a few values:

insert into employee(title,first_name,start_date,charge) values('Mr. X','Smith',date(now()),'None');
insert into employee(title,first_name,start_date,charge) values('Mr. Y','Smith',date(now()),'None');
insert into employee(title,first_name,start_date,charge) values('Mr. Z','Smith',date(now()),'None');

so far there is no problem at all, the problem comes here:

select title || ' ' || first_name || ' ' || last_name as fullname from employee;
  fullname   
----------------  (3 rows)

Doesn't work !!!!, I'm thinking it is because of the null value in last_name.  Have any idea or suggestion on how to
workaroundthis situation.
 

Thank you.

--
Luis Magaña
Gnovus Networks & Software
www.gnovus.com
Tel. +52 (7) 4422425
joe666@gnovus.com

Luis Magaña wrote:
> 
> Hi:
> 
> Have this curious situation and would like some help from you:
> 
> Create an employee table:
> 
> CREATE  TABLE  employee(
>    id_employee SERIAL PRIMARY KEY,
>    sex CHAR(1) DEFAULT 'm' CHECK(sex = 'f' OR sex = 'm'),
>    start_date DATE NOT NULL,
>    charge VARCHAR(50) NOT NULL,
>    last_name VARCHAR(50),
>    first_name VARCHAR(50) NOT NULL,
>    title VARCHAR(10) NOT NULL
> );
> 
> then fill it with a few values:
> 
> insert into employee(title,first_name,start_date,charge) values('Mr. X','Smith',date(now()),'None');
> insert into employee(title,first_name,start_date,charge) values('Mr. Y','Smith',date(now()),'None');
> insert into employee(title,first_name,start_date,charge) values('Mr. Z','Smith',date(now()),'None');
> 
> so far there is no problem at all, the problem comes here:
> 
> select title || ' ' || first_name || ' ' || last_name as fullname from employee;
> 
>    fullname
> ----------------
> 
> 
> 
>  (3 rows)
> 
> Doesn't work !!!!, I'm thinking it is because of the null value in last_name.  Have any idea or suggestion on how to
workaroundthis situation.
 

Yup.. it's due to the null..  I believe that the coalesce function can
get you out of this...  Speaking of which, why isn't it called NVL()?


http://www.postgresql.org/users-lounge/docs/7.0/user/functions.htm


Try this (untested):

select coalesce(title, ''::varchar) || ' ' || coalesce(first_name,
''::varchar) || ' ' || coalesce(last_name, ''::varchar) as fullname from
employee;

Rod Taylor <rbt@zort.on.ca> writes:
> I believe that the coalesce function can
> get you out of this...  Speaking of which, why isn't it called NVL()?

Because the SQL92 standard calls it coalesce.
        regards, tom lane

That would be an extreamly good reason then.  I suppose I've fallen into
the 'other' standard :(

Tom Lane wrote:
> 
> Rod Taylor <rbt@zort.on.ca> writes:
> > I believe that the coalesce function can
> > get you out of this...  Speaking of which, why isn't it called NVL()?
> 
> Because the SQL92 standard calls it coalesce.
> 
>                         regards, tom lane

At 05:47 PM 11/8/00 -0600, Luis Magaña wrote:

>insert into employee(title,first_name,start_date,charge) values('Mr.
X','Smith',date(now()),'None');
>insert into employee(title,first_name,start_date,charge) values('Mr.
Y','Smith',date(now()),'None');
>insert into employee(title,first_name,start_date,charge) values('Mr.
Z','Smith',date(now()),'None');
>
>so far there is no problem at all, the problem comes here:
>
>select title || ' ' || first_name || ' ' || last_name as fullname from
employee;
>
>   fullname   
>----------------
>  
> 
>  
> (3 rows)
>
>Doesn't work !!!!, I'm thinking it is because of the null value in last_name.

Right.  NULL means "has no value", it's not the empty string.  The result of
concatenating with NULL is NULL.

> Have any idea or suggestion on how to workaround this situation.

It's a classic NULL issue.  1+NULL = NULL, too, for instance.  Try
something like
"default ''" in your table definition rather than use null.  Then you'll be
using
the empty string for concatenation.  'abc' || NULL = NULL.  'abc' || '' =
'abc'
which appears to be what you want.

This is standard SQL92 behavior...




- Don Baccus, Portland OR <dhogaza@pacifier.com> Nature photos, on-line guides, Pacific Northwest Rare Bird Alert
Serviceand other goodies at http://donb.photo.net.