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;

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:

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.