Thread: concat fails 'sometimes' on empty colums in select

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)

On Sat, Dec 11, 2004 at 03:33:07 +0100,
  Stefan de Konink <skinkie@xs4all.nl> wrote:
> 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.

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.

On Sat, Dec 11, 2004 at 01:54:35PM -0600, Bruno Wolff III 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.

To simplify this operation you could create an operator that does
the coalesce for you.  This has come up recently; search the list
archives for examples.

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

Stefan,

> To simplify this operation you could create an operator that does
> the coalesce for you. =C2=A0This has come up recently; search the list
> archives for examples.

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

--=20
Josh Berkus
Aglio Database Solutions
San Francisco