Re: duplicate rows mystery

How about the simple case that pricebackup is not empty before the
select? You say it is a new table, but did you do a test run or anything
like that before the first select? How about dropping or truncating the
table and starting again?


On Thu, 2005-04-14 at 18:09, Gerry Jensen wrote:
> 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/
> >
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
>                http://archives.postgresql.org
--


Regards,

Steve Tucknott
ReTSol Ltd

DDI    01903 828769
MOBILE    07736715772