duplicate rows mystery

From: Gerry Jensen <gerry(at)xmission(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Subject: duplicate rows mystery
Date: 2005-04-14 09:26:16
Message-ID: Pine.LNX.4.62.0504140314240.5542@xmission.xmission.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

I'm trying to remove duplicate records from a table. The best way I could
think of was to select into a new table using DISTINCT.

I issued the following command:

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.

How is it possible that I could have duplicate values in that table when I
just selected them into the table as distinct? What am I missing?

Any help is appreciated.

Thanks,

Gerry

Responses

Browse pgsql-novice by date

  From Date Subject
Next Message rui.justino 2005-04-14 09:28:39 inset html data into text column
Previous Message rui.justino 2005-04-14 09:25:18