pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedConcatenating not working properly
3+ messages / 3 participants
[nested] [flat]
* Concatenating not working properly
@ 2003-04-09 14:54 Michal Taborsky <M.Taborsky@sh.cvut.cz>
2003-04-09 15:17 ` Re: Concatenating not working properly Tomasz Myrta <jasiek@klaster.net>
2003-04-09 15:18 ` Re: Concatenating not working properly Joe Conway <mail@joeconway.com>
0 siblings, 2 replies; 3+ messages in thread
From: Michal Taborsky @ 2003-04-09 14:54 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Concatenating not working properly
2003-04-09 14:54 Concatenating not working properly Michal Taborsky <M.Taborsky@sh.cvut.cz>
@ 2003-04-09 15:17 ` Tomasz Myrta <jasiek@klaster.net>
1 sibling, 0 replies; 3+ messages in thread
From: Tomasz Myrta @ 2003-04-09 15:17 UTC (permalink / raw)
To: Michal Taborsky <M.Taborsky@sh.cvut.cz>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Concatenating not working properly
2003-04-09 14:54 Concatenating not working properly Michal Taborsky <M.Taborsky@sh.cvut.cz>
@ 2003-04-09 15:18 ` Joe Conway <mail@joeconway.com>
1 sibling, 0 replies; 3+ messages in thread
From: Joe Conway @ 2003-04-09 15:18 UTC (permalink / raw)
To: Michal Taborsky <M.Taborsky@sh.cvut.cz>; +Cc: pgsql-sql
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 NULL FROM 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, secondname FROM person;
HTH,
Joe
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2003-04-09 15:18 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-04-09 14:54 Concatenating not working properly Michal Taborsky <M.Taborsky@sh.cvut.cz>
2003-04-09 15:17 ` Tomasz Myrta <jasiek@klaster.net>
2003-04-09 15:18 ` Joe Conway <mail@joeconway.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox