Re: restore/dup OIDs HELP! - Mailing list pgsql-admin

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@postgresql.org so that your
>> message can get through to the mailing list cleanly
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>