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@postgresql.org so that your
> message can get through to the mailing list cleanly