to_char and '=' weirdness

From: Frank Joerdens <frank(at)joerdens(dot)de>
To: pgsql-sql(at)postgresql(dot)org
Subject: to_char and '=' weirdness
Date: 2002-08-08 11:33:00
Message-ID: 20020808133300.A15630@superfly.archi-me-des.de
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

German area codes from the former east all have a leading zero. As I
store area codes as integers, I use to_char to find those with a leading
zero, as in

select * from adressen where to_char(plz, '00000') like '%04109%';

(this would be Leipzig)

What I don't understand is why something like

select * from adressen where to_char(plz, '00000') = '04109';

will yield 0 rows. If I do

archi=# select to_char(plz, '00000') from adressen where
archi-# to_char(plz, '00000') like '%04109%';

I get:

to_char
---------
04109
04109
04109
04109
04109
04109
04109
04109
04109
04109
04109
04109
04109
04109
04109
(15 rows)

(i.e. my database contains 15 addresses from Leipzig)

which would appear to indicate that to_char actually yields the string
'04109'. Why doesn't the '=' operator work then?

Regards, Frank

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Ludwig Lim 2002-08-08 11:48:24 Re: to_char and '=' weirdness
Previous Message Richard Huxton 2002-08-08 09:20:03 Re: Effective usage without unique key -- suggestion