Postgres: Getting the "partition number" instead of row_number Figure a table with the following data, how to exclude 'alone' values?

id; data
(1, 'foo'),
(2, 'foo'),
(3, 'foo'),
(4, 'bar'),
(5, 'bar');
(6, 'jak');


I try with this:

select id, data, row_number() over (partition by data)
from t;

 -- RESULT
id  data    row_number
4   bar     1
5   bar     2
1   foo     1
2   foo     2
3   foo     3
6   jak     1

 -- EXPECTED (exclude partition with one row)
id  data    row_number
4   bar     1
5   bar     2
1   foo     1
2   foo     2
3   foo     3


row_number() gives an index to rows in each partition.

How to have the number of rows by partition? Some kind of WHERE partition_count &gt; 1.

fiddle
Use count as a window function:

SELECT id, data
FROM (SELECT id, data,
             count(*) OVER (PARTITION BY data) AS c
      FROM t) AS q
WHERE c &gt; 1;