Divide records into groups - quick solution

grouping, postgresql, random, select, sequence

Solution

The query below should display 213 rows with a group-number from 0-4. Just add 1 if you want 1-5

SELECT i, (row_number() OVER () - 1) / 50 AS grp 
FROM generate_series(1001,1213) i 
ORDER BY i;

Problem

I need to divide with `UPDATE` command rows (selected from subselect) in PostgreSQL table into groups, these groups will be identified with integer value in one of its columns. These groups should be with the same size. Source table contains billions of records. For example I need to divide 213 selected rows into groups, every group should contains 50 records. The result will be: - 1 - 50. row => 1 - 51 - 100. row => 2 - 101 - 150. row => 3 - 151 - 200. row => 4 - 200 - 213. row => 5 There is no problem to do it with some loop (or use PostgreSQL window functions), but I need to do it very efficiently and quickly. I can't use sequence in id because there should be gaps in these ids. I have an idea to use random integer number generator and set it as default value for a row. But this is not useable when I need to adjust group size.

Original source