Thread: select distinct error ?
hello all, i am just experiencing something weird: select distinct foo from bar where foobar like 'foobar%'; gives: foo -------- 090700 090701 090702 090700 (4 rows) foo is character(6) How can this happen?? Mit freundlichem Gruß Henrik Steffen Geschäftsführer top concepts Internetmarketing GmbH Am Steinkamp 7 - D-21684 Stade - Germany -------------------------------------------------------- http://www.topconcepts.com Tel. +49 4141 991230 mail: steffen@topconcepts.com Fax. +49 4141 991233 -------------------------------------------------------- 24h-Support Hotline: +49 1908 34697 (EUR 1.86/Min,topc) -------------------------------------------------------- System-Partner gesucht: http://www.franchise.city-map.de -------------------------------------------------------- Handelsregister: AG Stade HRB 5811 - UstId: DE 213645563 --------------------------------------------------------
> i am just experiencing something weird: > > select distinct foo from bar where foobar like 'foobar%'; > > gives: > > foo > -------- > 090700 > 090701 > 090702 > 090700 > (4 rows) > try: SELECT distinct foo, length(foo) from bar where foobar like 'foobar%'; maybe one has a blank attached?
"Mario Weilguni" <mweilguni@sime.com> writes: > try: > SELECT distinct foo, length(foo) from bar where foobar like 'foobar%'; > maybe one has a blank attached? He said the column was char(6), so they should all be padded to length 6. I was wondering about nonprinting characters (carriage returns etc) in the data, myself. regards, tom lane
On Thu, 25 Jul 2002, Henrik Steffen wrote: > select distinct foo from bar where foobar like 'foobar%'; > > gives: > > foo > -------- > 090700 > 090701 > 090702 > 090700 > (4 rows) > > foo is character(6) > > How can this happen?? Do you have a simple dataset that you can replicate this with that you can send a dump of (tables, data). Also, what version and what locale are you running with?
hello, I did now: select foo,count(foobar),length(foo) from bar where foobar like 'foobar%' group by foo; foo | count | length ------------------------ 090700 | 494 | 6 090701 | 6 | 6 090702 | 4 | 6 090700 | 237 | 6 AAAAAAAAAA!!!!!! now I found it!!!! User-input error: someone entered O90700 instead of 090700 with an 'O' (big 'o') instead of '0' (zero).... going crazy here..... excuse me for this stupid question.... Mit freundlichem Gruß Henrik Steffen Geschäftsführer top concepts Internetmarketing GmbH Am Steinkamp 7 - D-21684 Stade - Germany -------------------------------------------------------- http://www.topconcepts.com Tel. +49 4141 991230 mail: steffen@topconcepts.com Fax. +49 4141 991233 -------------------------------------------------------- 24h-Support Hotline: +49 1908 34697 (EUR 1.86/Min,topc) -------------------------------------------------------- System-Partner gesucht: http://www.franchise.city-map.de -------------------------------------------------------- Handelsregister: AG Stade HRB 5811 - UstId: DE 213645563 -------------------------------------------------------- ----- Original Message ----- From: "Stephan Szabo" <sszabo@megazone23.bigpanda.com> To: "Henrik Steffen" <steffen@city-map.de> Cc: "pg" <pgsql-general@postgresql.org>; "Sönke Ruempler" <soenke@ruempler.de> Sent: Thursday, July 25, 2002 5:55 PM Subject: Re: [GENERAL] select distinct error ? > On Thu, 25 Jul 2002, Henrik Steffen wrote: > > > select distinct foo from bar where foobar like 'foobar%'; > > > > gives: > > > > foo > > -------- > > 090700 > > 090701 > > 090702 > > 090700 > > (4 rows) > > > > foo is character(6) > > > > How can this happen?? > > Do you have a simple dataset that you can replicate this with > that you can send a dump of (tables, data). Also, what version > and what locale are you running with? > > > >
How about: SELECT distinct foo, length(foo) from bar where foobar like 'foobar%' and foobar ~'^[0-9]*$'; I have a feeling that you may have an O in stead of a zero. Tom Lane wrote: > > "Mario Weilguni" <mweilguni@sime.com> writes: > > try: > > SELECT distinct foo, length(foo) from bar where foobar like 'foobar%'; > > maybe one has a blank attached? > > He said the column was char(6), so they should all be padded to length > 6. I was wondering about nonprinting characters (carriage returns etc) > in the data, myself. > > regards, tom lane > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html