Re: DISTINCT is not quite distinct

Florian Weimer wrote:
> 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.

Could be index corruption perhaps.

I take it SELECT DISTINCT bar... shows the same problem?

If so, can you do:
  SELECT OID,xmin,cmin,xmax,cmax,bar FROM baz
   WHERE bar = <something with duplicates>

--
   Richard Huxton
   Archonet Ltd