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 workaround this situation.

Thank you.

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



Wed, 08 Nov 2000 17:47:44 -0600
Luis =?UNKNOWN?Q?Maga=F1a?= <joe666@gnovus.com>
Text concat problem
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 workaround this 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;

Wed, 08 Nov 2000 23:34:36 -0500
Rod Taylor <rbt@zort.on.ca>
Re: Text concat problem
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

Wed, 08 Nov 2000 23:38:18 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Text concat problem 
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

Wed, 08 Nov 2000 23:44:06 -0500
Rod Taylor <rbt@zort.on.ca>
Re: Text concat problem
At 05:47 PM 11/8/00 -0600, Luis =?UNKNOWN?Q?Maga=F1a?= 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 Service and other goodies at
  http://donb.photo.net.

Thu, 09 Nov 2000 06:20:57 -0800
Don Baccus <dhogaza@pacifier.com>
Re: Text concat problem