From: Maximilian Tyrtania <maximilian.tyrtania@onlinehome.de>
To: Scott Marlowe <scott.marlowe@gmail.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Distinct oddity
Date: Fri, 08 May 2009 11:28:40 +0200
Message-ID: <C629C7E8.35E64%maximilian.tyrtania@onlinehome.de> (raw)
In-Reply-To: <dcc563d10905071154j613847edqc5cab8c8af68d496@mail.gmail.com>
am 07.05.2009 20:54 Uhr schrieb Scott Marlowe unter scott.marlowe@gmail.com:
> On Thu, May 7, 2009 at 2:21 AM, Maximilian Tyrtania
> <maximilian.tyrtania@onlinehome.de> wrote:
>> Hi there,
>>
>> does this look right?
>>
>> FAKDB=# select count(distinct(f.land)) from firmen f where
>> f.typlist='Redaktion';
>> count
>> -------
>> 1975
>> (1 row)
>>
>> FAKDB=# select count(distinct(f.land||'1')) from firmen f where
>> f.typlist='Redaktion';
>> count
>> -------
>> 4944
>> (1 row)
>
> Yeah, that does seem odd. Could it be something like nulls in your
> data set? just guessing really. If you could make a small test case
> that shows it happening and allows others to reproduce it you're
> likely to get more bites.
It doesn't seem to be related to null values (which wouldn't explain it
anyway) nor to this particular field...
FAKDB=# select count(*) from firmen where bezeichnung is null;
count
-------
0
(1 row)
FAKDB=# select count(distinct(f.bezeichnung)) from firmen f;
count
-------
72698
(1 row)
FAKDB=# select count(distinct(f.bezeichnung||'e')) from firmen f;
count
-------
72892
(1 row)
My attempts at reproducing this with a freshly created table failed, of
course.
FAKDB=# create table concattest(mytext text);
CREATE TABLE
FAKDB=# insert into concattest (mytext)
select(generate_series(1,10000)::text);
INSERT 0 10000
FAKDB=# insert into concattest (mytext)
select(generate_series(1,10000)::text);
INSERT 0 10000
FAKDB=# select count(distinct(mytext)) from concattest;
count
-------
10000
(1 row)
FAKDB=# select count(distinct(mytext||'2')) from concattest;
count
-------
10000
(1 row)
best,
Maximilian Tyrtania
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: maximilian.tyrtania@onlinehome.de, scott.marlowe@gmail.com
Subject: Re: Distinct oddity
In-Reply-To: <C629C7E8.35E64%maximilian.tyrtania@onlinehome.de>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox