How to make this ActiveRecord query group in postgres

Question

I am trying to find a 3M table, all users having the same username. I read something like this, can do the trick.

User.find(:all, :group => [:username], :having => "count(*) > 1" ) 

However, since I'm using Postgres, return ActiveRecord::StatementInvalid: PG::Error: ERROR: column "users.id" must appear in the GROUP BY clause or be used in an aggregate function .

I'm trying something like this

 User.select('users.id, users.username').having("count(*) > 1").group('users.username') 

But still get the same error. Any idea what I'm doing wrong?

Update: I did this somehow using User.select('users.*').group('users.id').having('count(users.username) > 1') , but this request returns this to me that looks like an empty array, even if it contains 5 entries.

  GroupAggregate (cost=9781143.40..9843673.60 rows=3126510 width=1365) Filter: (count(username) > 1) -> Sort (cost=9781143.40..9788959.68 rows=3126510 width=1365) Sort Key: id -> Seq Scan on users (cost=0.00..146751.10 rows=3126510 width=1365) (5 rows) => [] 

Any idea why this is happening and how to get these 5 lines?

Answer 1

I think the best you could get is to get usernames for duplicate entries. This can be achieved with

 User.select(:username).group(:username).having('COUNT(username) > 1') 

Answer 2

"group by" in the database collapses each group into one line in the output. Most likely, what you intend to be prepared by the following request:

  User.where("name in (select name from users group by name having count(*)>1)").order(:name) 

The internal query above finds all names that appear more than once. Then we will find all rows with these names. An order by name will simplify your further processing. To speed things up, add an index to the column name in the user table.

There are alternative ways to post Postgres to solve this problem, however the above will work in all databases.