Message-ID: <3E94399C.9080009@klaster.net>
Date: Wed, 09 Apr 2003 17:17:48 +0200
From: Tomasz Myrta <jasiek@klaster.net>
User-Agent: Mozilla/5.0 (Windows; U; Win 9x 4.90; pl-PL;
 rv:1.3) Gecko/20030312
MIME-Version: 1.0
To: Michal Taborsky <M.Taborsky@sh.cvut.cz>
Cc: pgsql-sql@postgresql.org
Subject: Re: Concatenating not working properly
References: <3E94343A.1060108@sh.cvut.cz>
In-Reply-To: <3E94343A.1060108@sh.cvut.cz>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: quoted-printable

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

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

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

Regards,
Tomasz Myrta