Postgres - Lateral join with random values on both sides

Question

I am trying to generate the following table:

random person a | random utility 1
random person a | random utility 2
random person a | random utility 3
random person b | random utility 4
random person b | random utility 5
random person b | random utility 6

So, I want to generate two random persons and for each person, 3 random utilities. Generated utilities are not shared between persons.

I came up with the following query:

select              gen_random_uuid() person, utility.utility
from                generate_series(1, 2) person
inner join lateral  (select person, gen_random_uuid() utility from generate_series(1, 3)) utility
on                  person.person = utility.person;

But this generates the following:

64bf364b-ff74-4888-b2d9-0ffcf3147dd7 | f8455aa3-73ab-4432-9c49-2c940da28fa7
05f54217-0316-410d-83e8-2b5306eee143 | 68f7b48-2561-438c-a906-34b141c4adc5
07c23fc3-a6b9-4d74-a611-264c4de9a0bd | 5a597190-09f9-44ea-960d-f6657aa10a81
b5903b45-f96a-4eb4-8b67-95340131a29b | 742daba-de73-45c4-8b2b-758663c4af47
6ba75082-77e4-408b-9e19-92595121cf43 | 01635986-98e3-432e-9f4e-0a88edba3d67
48d7ab6f-4a32-4306-b060-f0019aef1d11 | 99f4e91f-c05f-496c-a4a3-7b62f64ed6e1

So, as you can see, the result contains 6 random persons, with each person his/her own random utility.

As said, I need 2 random persons with each 3 random utlities. How can I fix this query?

Answer 1

Your problem is that you are assigning the person uuid in the outermost select. So, you get one for each row.

My first thought is a bit more complicated:

with p as (
      select gen_random_uuid() as person, g.i
      from generate_series(1, 2) g(i)
     ),
     u as (
      select gen_random_uuid() as utility, g.i
      from generate_series(1, 6) g(i)
    )
select p.person, u.utility
from p join
     u
     on mod(p.i, 2) = mod(u.i, 2);

Admittedly, a lateral join is simpler, but a similar idea can be used:

with p as (
      select gen_random_uuid() as person, g.i
      from generate_series(1, 2) g(i)
     )
select p.person, u.utility
from p, lateral
     (select gen_random_uuid() as utility
      from generate_series(1, 3)
     ) u;

Or, without a lateral join at all:

with p as (
      select gen_random_uuid() as person, g.i
      from generate_series(1, 2) g(i)
     )
select p.person, gen_random_uuid() as utility
from p cross join
     generate_series(1, 3);