DISTINCT is not quite distinct

From: Florian Weimer <fweimer(at)bfk(dot)de>
To: pgsql-general(at)postgresql(dot)org
Subject: DISTINCT is not quite distinct
Date: 2006-11-06 13:51:38
Message-ID: 82lkmoy979.fsf@mid.bfk.de
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I run this innocent query

CREATE TABLE foo AS SELECT DISTINCT bar FROM baz ORDER BY bar;

and the resulting table contains duplicate rows. 8-(

According to EXPLAIN, an index scan on the bar column is used (using
the underlying B-tree index). This is with PostgreSQL 8.1.4 (Debian
package 8.1.4-6). Is this a known problem?

If I drop the DISTINCT, the output is not correctly ordered, either.
Perhaps this is an index corruption issue? The hardware itself seems
fine.

--
Florian Weimer <fweimer(at)bfk(dot)de>
BFK edv-consulting GmbH http://www.bfk.de/
Kriegsstraße 100 tel: +49-721-96201-1
D-76133 Karlsruhe fax: +49-721-96201-99

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Desmond Coughlan 2006-11-06 14:05:25 first steps in PhP and PostgreSQL
Previous Message Robert Bernier 2006-11-06 12:11:57 Re: Need replacement booth member for USENIX Lisa