Re: restore/dup OIDs HELP!

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