SQL: Finding Duplicate Rows
<span class="k">A great way to detect duplicates in PostgreSQL is by using window functions.SELECT</span> <span class="n">id</span> <span class="k">from</span> <span class="p">(</span><span class="k">SELECT</span> <span class="n">id</span><span class="p">,</span> <span class="n">ROW_NUMBER</span><span class="p">()</span> <span class="n">OVER</span><span class="p">(</span>
<span class="n">PARTITION</span> <span class="k">BY</span> <span class="n">first_name</span><span class="p">,</span> <span class="n">last_name</span><span class="p">,</span> <span class="n">email</span>
<span class="k">ORDER</span> <span class="k">BY</span> <span class="n">id</span>
<span class="p">)</span>
<span class="k">AS</span> <span class="n">user_row_number</span> <span class="k">FROM</span> <span class="n">users</span> <span class="p">)</span> <span class="n">duplicates</span> <span class="k">WHERE</span> <span class="n">duplicates</span><span class="p">.</span><span class="n">user_row_number</span> <span class="o">></span> <span class="mi">1</span>