Select rows with different id but same values

Question

I would like to find all records with the same values but different id's.

Here is the data I currently have:

+------+------+
|id    |userid|
+------+------+
|1     | 5    |
+------+------+
|1     | 8    |
+------+------+
|2     | 10   |
+------+------+
|2     | 12   |
+------+------+
|3     | 5    |
+------+------+
|3     | 8    |
+------+------+
|4     | 8    |
+------+------+
|4     | 13   |
+------+------+

id 1 has the same exact userid values as id 3. I want to see all the tables that have the same userid values.

Here is the results I would like it to return:

+------+------+
|id    |userid|
+------+------+
|1     | 5    |
+------+------+
|1     | 8    |
+------+------+
|3     | 5    |
+------+------+
|3     | 8    |
+------+------+

Im using Postgres 9.6.

Answer 1

One possible way is to use aggregation and array_agg() to get an array of the user IDs per ID. You can then inner join back to the original table and use EXISTS to filter for different IDs that share the same array, i.e. user ID. For convenience the derived table containing the aggregation can be put in a CTE.

WITH cte
AS
(
SELECT t.id,
       array_agg(t.userid ORDER BY t.userid) userids
       FROM elbat t
       GROUP BY id
)
SELECT t1.*
       FROM elbat t1
            INNER JOIN cte c1
                       ON c1.id = t1.id
       WHERE EXISTS (SELECT *
                            FROM cte c2
                            WHERE c2.id <> c1.id
                                  AND c2.userids = c1.userids);

db<>fiddle

Answer 2

First you have to filter the userid that appears more than one time:

select
userid
from table
group by userid
having count(1) > 1

This will return:

userid
------
     5
     8

Now using this as a subquery you can select the complete result you want:

select 
*
from table
where userid in (
  select
  userid
  from table
  group by userid
  having count(1) > 1
) 

Answer 3

WITH cte AS (
SELECT id,
       userid,
       string_agg(userid::text, ',') OVER (PARTITION BY id 
                                           ORDER BY userid 
                                           ROWS BETWEEN UNBOUNDED PRECEDING 
                                                    AND UNBOUNDED FOLLOWING) total
FROM test
)
SELECT DISTINCT cte1.id, cte1.userid /* , cte1.total */
FROM cte cte1, cte cte2
WHERE cte1.total = cte2.total
  AND cte1.id != cte2.id
ORDER BY cte1.id, cte1.userid