Subject Getting duplicate records in a table
Author Adam
Hello Group,

I have the following scenario that although I can solve it two ways so
far, I am not happy with the plan it generates.


ID FieldA FieldB
-- ------ ------
1 1 2
2 1 2
3 3 4
4 1 2
5 3 5
6 3 5
7 1 7

Basically, I need to get all records with duplicated FieldA, FieldB

The query will end up in a PSQL for select block.

I will then remove these records, and insert a new record for this
combination. (for example, it would remove records 1,2 and 4 and
insert record ID 8 with 1,2).

(This is a notification table)

I can do the following:

select FieldA, FieldB
from TableA
group by 1,2
having count(*)>1

And get the right records, but I don't really need to visit and count
each of the records (I just need to know if at least one other
matching record exists).

I have tried several variations of joining back to itself and using
exists clauses but they still need to do a natural read of TableA, and
if it is going to do that, then the count(*) is just as efficient.

Any ideas are appreciated.