Hi,

I have a table with some empty colums, if I try to concat my colums to
eachother something strange happens. Rather empty column B produces
virtually no output using the || in the select query. But the half filed
colum A does give the desired output.


midvliet=# select snr, achternaam||', '||voornaam||' '||tussenvoegsels,
tussenvoegsels, straatnaam||' '||huisnummer||' '||toevoeging,
postcodecijfers||' '||postcodeletters, woonplaats from naw;
snr | ?column? | tussenvoegsels | ?column? |
?column? | woonplaats
-----+-------------------------+----------------+-----------------+----------+--------------
18 | Konink, Stefan de | de | |
2265 CA | Leidschendam
19 | Köler, Julie | | |
2261 XK | Leidschendam

As you see two people one with 'tussenvoegsels' and one without. As you
also see neighter of those users has the next column.


midvliet=# select snr, achternaam, straatnaam||' '||huisnummer||'
'||toevoeging, straatnaam, huisnummer, toevoeging from naw;
snr | achternaam | ?column? | straatnaam |
huisnummer | toevoeging
-----+-------------+-----------------+------------------------------+------------+------------
18 | Konink | | Oude Trambaan |
7 |
19 | Köler | | Wildenborch |
81 |


Now if I enter some data in the 'toevoeging' column, it works. But this
behavior is not the same as previously with 'tussenvoegsels'.

midvliet=# \d naw
Table "public.naw"
Column | Type |
Modifiers
-----------------+-----------------------+------------------------------------------------------
snr | integer | not null default
nextval('public.naw_snr_seq'::text)
achternaam | character varying(25) |
voornaam | character varying(25) |
tussenvoegsels | character varying(10) |
straatnaam | character varying(50) |
huisnummer | smallint |
toevoeging | character varying(10) |
postcodecijfers | smallint |
postcodeletters | character(2) |
woonplaats | character varying(25) |
geboortedatum | date |
Indexes:
"naw_pkey" PRIMARY KEY, btree (snr)


I hope this is no stupid mistake of myself


Stefan de Konink

(Using Postgresql 8.0.0-rc1)

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo (AT) postgresql (DOT) org so that your
message can get through to the mailing list cleanly

On Sat, Dec 11, 2004 at 03:33:07 +0100,
Stefan de Konink <skinkie (AT) xs4all (DOT) nl> wrote:
Empty strings and NULL values aren't the same thing. You probably either
want to store empty strings or use coalesce to change NULL to an empty
string before using concatenation, as a NULL concatenated with anything
results in NULL.

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo (AT) postgresql (DOT) org

Stefan,

Actually, it's on General Bits:
http://www.varlena.com/varlena/GeneralBits/84.php

--=20
Josh Berkus
Aglio Database Solutions
San Francisco

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo (AT) postgresql (DOT) org