[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
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
a
anonymous
said
almost 13 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
a
anonymous
said
over 9 years ago
[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
anonymous