Re: restore/dup OIDs HELP!

Steve,

Thanks for the excellent suggestion!

Yes, that would have worked. I guess I did it the hard way. I wrote a
nice little perl script to locate the dups by OID and then pull the data,
re-insert it, and then delete the original dup by OID. It was smart, I
could run it as many times as needed and it would not have touched any
entry which was not dupped.

This is the SQL soltuion to such a problem. Thanks for the help!

Steve Crawford wrote:

> Would this work for you (if you can muck with the comm table temporarily
> without causing problems)?
>
> --Create a table with unique values
> create temporary table communique as select distinct * from comm;
>
> --Empty the table
> delete from comm;
> (or truncate comm if recent enough version or drop and recreate comm if
> it's really large and an older version of PostgreSQL)
>
> --Repopulate comm from commtemp
> insert into comm select * from commtemp;
>
> Cheers,
> Steve
>
> On Saturday 04 January 2003 10:30 pm, Jack Flak wrote:
>> Greetings Group,
>>
>> I'm running 7.1.
>>
>> Basically, my question is this: how do I delete an exact dup without
>> deleting the original?
>>
>> Let me explain...
>>
>> I just accidentally ran a restore on my perfectly running database. When
>> I originally made the dump file, I had OIDs turned on. I figured they
>> were unique. Now, after the restore is done, I see that they are not.
>>
>> So now I have duplicate entries in a large table. They are duplicate all
>> the way down to the OIDs! I was stupid enough not to set a unique key
>> field for this table when I designed it, so the system accepted the "new"
>> entries with the exact same OIDs as already existed. However, almost all
>> of my other tables do have unique keys set, so the dups were rejected.
>> Check out my stupidity:
>>
>>
>> # SELECT oid, sender, length(text), date_submitted FROM comm ORDER BY
>> sender, date_submitted LIMIT 20;
>> oid | sender | length | date_submitted
>> -------+--------+--------+------------------------
>> 61385 | 132 | 2179 | 2001-02-23 16:43:00-08
>> 61385 | 132 | 2179 | 2001-02-23 16:43:00-08
>> 61386 | 132 | 1313 | 2001-02-25 17:40:00-08
>> 52234 | 154 | 2073 | 2001-05-07 23:40:00-07
>> 52234 | 154 | 2073 | 2001-05-07 23:40:00-07
>> 49588 | 168 | 3063 | 2002-03-20 12:04:00-08
>> 49588 | 168 | 3063 | 2002-03-20 12:04:00-08
>> 49592 | 168 | 5243 | 2002-03-26 10:54:00-08
>> 49592 | 168 | 5243 | 2002-03-26 10:54:00-08
>> 49801 | 188 | 1010 | 2000-08-22 12:30:00-07
>> 49801 | 188 | 1010 | 2000-08-22 12:30:00-07
>> 49802 | 188 | 307 | 2000-08-22 12:37:00-07
>> 49802 | 188 | 307 | 2000-08-22 12:37:00-07
>> 49803 | 188 | 1790 | 2000-08-22 12:39:00-07
>> 49803 | 188 | 1790 | 2000-08-22 12:39:00-07
>> 49804 | 188 | 531 | 2000-08-22 12:41:00-07
>> 49804 | 188 | 531 | 2000-08-22 12:41:00-07
>> 49805 | 188 | 4700 | 2000-08-22 12:45:00-07
>> 49805 | 188 | 4700 | 2000-08-22 12:45:00-07
>> 49809 | 188 | 2855 | 2000-08-22 12:47:00-07
>> (20 rows)
>>
>>
>> The 'sender' field, plus the 'date_submitted' field are used as "the key"
>> for each entry.
>> If you're sharp, you'll notice that for 'sender' 132, there are three
>> total, one dup. This is because I tried to delete one of the dups. This
>> was a mistake, as BOTH were deleted. I re-added it again from the dump
>> file manually (boy, that was really not fun).
>>
>> So, once again, here's my question: how do I go about deleting the
>> duplicate entries WITHOUT also deleting the originals?
>>
>>
>>
>> ---------------------------(end of broadcast)---------------------------
>> TIP 3: if posting/reading through Usenet, please send an appropriate
>> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
>> message can get through to the mailing list cleanly
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>