select distinct error ?

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 ?

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: