Re: duplicate rows mystery

Sorry. That was a typo in my post. I switched the table names in my
SELECT INTO line in my post (I tested it again to make sure that's not
what I did in psql and my results were the same). price was the original
table with duplicate rows. What I really did was:

select distinct on (symbol, date) * into pricebackup from price;

then:

create unique index price_symbol_date on pricebackup (symbol, date);

Yet it still gave me the duplicate value errors. How is this possible?

Thanks,

Gerry

On Thu, 14 Apr 2005, Michael Fuhr wrote:

> On Thu, Apr 14, 2005 at 03:26:16AM -0600, Gerry Jensen wrote:
>>
>> select distinct on (symbol, date) * into price from pricebackup;
>>
>> It did indeed remove several rows that had duplicate items. I then tried
>> to build a unique index on the columns (symbol, date) in pricebackup with:
>>
>> create unique index price_symbol_date on pricebackup (symbol, date);
>>
>> and got the error:
>>
>> ERROR: could not create unique index
>> DETAIL: Table contains duplicated values.
>
> If the SELECT and CREATE statements are what you really did, then
> you tried to create an index on the original table (pricebackup),
> not the table you SELECTed into (price). Are you sure you have the
> right table names in the right places?
>
> --
> Michael Fuhr
> http://www.fuhr.org/~mfuhr/
>