Find rows with multiple duplicate fields with Active Record, Rails & Postgres
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.
That error occurs because POSTGRES requires you to put grouping columns in the SELECT clause.
try:
User.select(:first,:email).group(:first,:email).having("count(*) > 1").all
(note: not tested, you may need to tweak it)
EDITED to remove id column
If you need the full models, try the following (based on @newUserNameHere's answer).
User.where(email: User.select(:email).group(:email).having("count(*) > 1").select(:email))
This will return the rows where the email address of the row is not unique.
I'm not aware of a way to do this over multiple attributes.