PostgreSQL Repeated Rows

Question

I am using php to retrieve PostgreSQL data from a table, the data cannot be repeated, I already have a way of getting data without repeated rows

SELECT DISTINCT realpath FROM paths WHERE fn='$fn'

what i need is a way of knowing which are the repeated rows

Vao Tsun · Accepted Answer · 2017-05-12 10:38:04Z

2

with a as (
  SELECT count(1),realpath 
  FROM paths WHERE fn='$fn'
  GROUP BY realpath 
)
select realpath 
from a
where count > 1

updtae As Abelisto suggested, my attempt to make it more clear using CTE might fail, so shorter and better way:

  SELECT count(1),realpath 
  FROM paths WHERE fn='$fn'
  GROUP BY realpath 
  HAVING count(1) > 1

answered May 12, 2017 at 9:51

Vao Tsun

52.4k13 gold badges114 silver badges149 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Why not just use having count(*) > 1?

dunno. just thought it will be clearer to OP. of course having is shorter... I'll probably edit to have both, you are right