restore/dup OIDs HELP!

Поиск
Список
Период
Сортировка
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?



В списке pgsql-admin по дате отправления:

Предыдущее
От: "Theodore A. Jencks"
Дата:
Сообщение: pg_restore not working.
Следующее
От: "David F. Skoll"
Дата:
Сообщение: Re: restore/dup OIDs HELP!