Обсуждение: Concatenating not working properly

Hello,

I am facing a problem I cannot really explain to myself. I have a table 
with personal data which has columns surname, firstname, secondname, 
title and I want to do a simple select like this:

SELECT (surname || ', ' || firstname || ' ' || secondname) as fullname 
FROM person

For some rows (from what I recognised it is with rows, which have the 
title column empty) it works, for some it returns empty string:

akcent=# SELECT (surname || ', ' || firstname || ' ' || secondname) as 
fullname, title_pre, surname, firstname, secondname FROM person;
       fullname        | title_pre | surname  | firstname | secondname
-----------------------+-----------+----------+-----------+------------                       | Ing.      | Taborsky |
Michal   |                       | Ing.      | Barta    | David     | Novak, Josef          |           | Novak    |
Josef    | Saroch, Adrian Walter |           | Saroch   | Adrian    | Walter
 
(4 rows)

Is that a bug or am I missing something ?

Thanks,
Michal

Uz.ytkownik Michal Taborsky napisa?:
> Hello,
>
> I am facing a problem I cannot really explain to myself. I have a table
> with personal data which has columns surname, firstname, secondname,
> title and I want to do a simple select like this:
>
> SELECT (surname || ', ' || firstname || ' ' || secondname) as fullname
> FROM person
>
> For some rows (from what I recognised it is with rows, which have the
> title column empty) it works, for some it returns empty string:
>
> akcent=# SELECT (surname || ', ' || firstname || ' ' || secondname) as
> fullname, title_pre, surname, firstname, secondname FROM person;
>
>        fullname        | title_pre | surname  | firstname | secondname
> -----------------------+-----------+----------+-----------+------------
>                        | Ing.      | Taborsky | Michal    |
>                        | Ing.      | Barta    | David     |
>  Novak, Josef          |           | Novak    | Josef     |
>  Saroch, Adrian Walter |           | Saroch   | Adrian    | Walter
> (4 rows)
>
> Is that a bug or am I missing something ?
>
> Thanks,
> Michal
It works as it should.

If you want to add null values, use:
SELECT coalesce(surname,'') || ', ' || coalesce(firstname,'') || ' ' ||
coalesce(secondname,'') as fullname> FROM person

Remember - NULL value means that value doesn't exist, so you can't add
"not existence" to other existing values.

Regards,
Tomasz Myrta

Michal Taborsky wrote:
> akcent=# SELECT (surname || ', ' || firstname || ' ' || secondname) as 
> fullname, title_pre, surname, firstname, secondname FROM person;
> 
>        fullname        | title_pre | surname  | firstname | secondname
> -----------------------+-----------+----------+-----------+------------
>                        | Ing.      | Taborsky | Michal    |
>                        | Ing.      | Barta    | David     |
>  Novak, Josef          |           | Novak    | Josef     |
>  Saroch, Adrian Walter |           | Saroch   | Adrian    | Walter
> (4 rows)
> 
> Is that a bug or am I missing something ?
> 

Try this:
SELECT (surname || ', ' || firstname || ' ' || secondname) as fullname,  title_pre, surname, firstname, secondname IS
NULLFROM person;
 

You'll find that when secondname is null, so is fullname. Instead of 
your original query, do (untested):

SELECT (surname || ', ' || firstname || ' ' || COALESCE(secondname, '')) as fullname, title_pre, surname, firstname,
secondnameFROM person;
 


HTH,

Joe