Find rows with multiple duplicate fields with Active Record, Rails & Postgres
activerecord, postgresql, ruby-on-rails
Solution
Tested & Working Version
User.select(:first,:email).group(:first,:email).having("count(*) > 1")
Also, this is a little unrelated but handy. If you want to see how times each combination was found, put .size at the end:
User.select(:first,:email).group(:first,:email).having("count(*) > 1").size
and you'll get a result set back that looks like this:
{[nil, nil]=>512,
["Joe", "test@test.com"]=>23,
["Jim", "email2@gmail.com"]=>36,
["John", "email3@gmail.com"]=>21}
Thought that was pretty cool and hadn't seen it before.
Credit to Taryn, this is just a tweaked version of her answer.
Problem
What is the best way to find records with duplicate values across multiple columns using Postgres, and Activerecord? I found this solution here: `User.find(:all, :group => [:first, :email], :having => "count(*) > 1" )` But it doesn't seem to work with postgres. I'm getting this error: PG::GroupingError: ERROR: column "parts.id" must appear in the GROUP BY clause or be used in an aggregate function