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
