postgresql find duplicates in column with ID

Question

For instance, I have a table say "Name" with duplicate records in it:

Id    |  Firstname
--------------------
1     |  John
2     |  John
3     |  Marc
4     |  Jammie
5     |  John
6     |  Marc

How can I fetch duplicate records and display them with their receptive primary key ID?

Answer 1

Use Count()Over() window aggregate function

Select * from
(
select Id, Firstname, count(1)over(partition by Firstname) as Cnt
from yourtable
)a
Where Cnt > 1