[This topic is migrated from our old forums. The original author name has been removed]
I was having trouble deleting duplicate data from a table (postgresql). I realized this was because these days Postgres tables are by default created without OID. Once I created a table WITH OID, I could delete my duplicates from the data grid.
The other work-around is to create an extra column with a unique key. So, not a huge problem.
I was curious, so here is the experiment I did in both SQuirreL and DBVisualizer
create table testTable (xyz Number(5,0), abc VARCHAR(30), pdq VARCHAR(30), RUP Number(5,0);
insert into testTable values (44,'frank','mather',5656);insert into testTable values (44,'frank','mather',5656);
insert into testTable values (44,'frank','mather',5656);insert into testTable values (44,'frank','mather',5656);
insert into testTable values (44,'frank','mather',5656);insert into testTable values (44,'frank','mather',5656);
insert into testTable values (44,'frank','mather',5656);insert into testTable values (43,'frank','mather',5000);
In DBVisualizer:
I then tried to delete one of the identical records and got Error! Could not uniquely identify the row to delete.
In SQuirreL, I got a warning but then was allowed to delete the record I had selected.
Squirrel has this option to "show row numbers" So, I think SQuirreL somehow internally assigns row numbers and uses those to delete duplicate rows. (Is this possible in DBVisualizer?)
The exact same behavior is true for MYSQL.
I'm finding everything I used to do in SQuirrel (or PGAdminIII ) to be much easier in DBVisualizer but I thought I'd point this out and ask about it.
Edited by: Tim McGuire on Mar 17, 2010 11:10 PM
[This reply is migrated from our old forums. The original author name has been removed]
Re: deleting duplicate data from postgres
Try this out: http://technobytz.com/most-useful-postgresql-commands.html
Edited by: vipin4u on Sep 14, 2013 7:24 PM
a
anonymous
said
over 14 years ago
[This reply is migrated from our old forums. The original author name has been removed]
Re: deleting duplicate data from postgres
Roger, I think dbvisualizer already supports Rownum (rowid in the case of Oracle and OID in the case of postgesql) for databases that have them.
Otherwise, you have answered my question. Tim
Roger Bjärevall
said
over 14 years ago
[This reply is migrated from our old forums.]
Re: deleting duplicate data from postgres
Tim,
DbVisualizer don't use any ROWNUM column to identify a row. The current strategy using a primary key or unique index is sufficient in most cases.
I will register a RFE so that DbVisualizer can use ROWNUM for databases supporting it.
Best Regards
Roger
anonymous