You forgot to mention that all the functions/views that utilized that table also now point to the
original table with the changed name, because it doesn't store the table name, it stores the table oid.
Berend Tober wrote:
A. Kretschmer wrote:
am Wed, dem 13.09.2006, um 15:46:58 -0700 mailte Junkone folgendes:
hI
i have a bad situation that i did not have primary key. so i have a
table like this
colname1 colname2
1 apple
1 apple
2 orange
2 orange
It is a very large table. how do i remove the duplctes quickly annd
without much change.
begin;
alter table foo rename to tmp;
create table foo as select distinct * from tmp;
commit;
A couple potential problems here. First, you forgot to drop table tmp.
But maybe that is good thing because although the OP hasn't told us
anything else useful about the situation, and he has clearly contrived a
simplistic facsimile of his real problem, to be useful the table most
likely either has foreign key references, and/or is the primary key for
other table foreign keys. You're suggestion will break whatever
application this data base supports because all the foreign keys will
point to table tmp rather than foo afterwards. Similarly, there is the
problem of any indexes on the table that would be lost. But I suppose
one can make the point that your suggestion is a great solution, given
the contrived example and insufficient problem understanding presented
by the OP -- I really think he needs more help than he realizes.
Regards,
Berend Tober
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
