From: | Jack Flak <jack(at)flak(dot)nospam(dot)org> |
---|---|
To: | pgsql-admin(at)postgresql(dot)org |
Subject: | restore/dup OIDs HELP! |
Date: | 2003-01-05 06:30:23 |
Message-ID: | 3iQR9.6166$My.301037750@newssvr14.news.prodigy.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-admin |
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?
From | Date | Subject | |
---|---|---|---|
Next Message | Igor Georgiev | 2003-01-06 11:11:05 | Ре:Pgadmin II |
Previous Message | Gaetano Mendola | 2003-01-04 14:42:02 | Vacuum explained |