Re: duplicate rows mystery - Mailing list pgsql-novice

Yes.  I tried it several times, dropping the newly created table each
time.  I'm wondering if my database is just corrupt.

My indexes in my original table were definitely corrupt (I originally had
a unique (symbol, date) index).  I discovered this when certain queries
failed to return certain rows (e.g. selecting on a specific date), but
different queries did return those rows (e.g. selecting a range of dates
that included the missing date).  When I dropped the indexes, I was able
to again see the missing rows with the specific date query.  Apparently
the index corruption had allowed the duplicate rows to be inserted in the
first place.

So perhaps there's something seriously wrong with my database and I need
to start over from scratch.  The main reason for my post is that I wanted
to make sure I was understanding "DISTINCT ON" correctly and there was
nothing wrong with my logic.  If those things look fine and logical to
everyone, then my posting here has been beneficial to me.

Anyway, thanks for the suggestion.

Gerry

On Thu, 14 Apr 2005, Steve Tucknott wrote:

> 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
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>