Postgres - side join with random values on both sides
I am trying to create 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 create two random faces and for each person, 3 random utilities. The generated utilities are not shared between people.
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 faces, with each person having their own random utility.
As said, I need 2 random people with every 3 random values. How can I fix this request?
source to share
Your problem is that you are assigning the person
uuid in the outermost select
. So you end up with one for each line.
My first thought is a little 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);
Side join is admittedly 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, with no side 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);
source to share
Here's an example (I'm using md5
instead gen_random_uuid
, but the same idea):
t=# with
person as (select md5(random()::text) from generate_series(1,2,1))
, utility as (select md5(random()::text) from generate_series(1,3,1))
select
person.md5, utility.md5
from person
join utility on true
;
md5 | md5
----------------------------------+----------------------------------
74df2447c58a1595e0a8458d1142e2e0 | 3703e2004a1494e6dd74cd51f4dd029e
74df2447c58a1595e0a8458d1142e2e0 | a1f7d74adc1ff3a49533204071e00f82
74df2447c58a1595e0a8458d1142e2e0 | 4f32029c29eee254d9c97045d06bcdf5
48a56953721f04752b325c332e26a5ed | 3703e2004a1494e6dd74cd51f4dd029e
48a56953721f04752b325c332e26a5ed | a1f7d74adc1ff3a49533204071e00f82
48a56953721f04752b325c332e26a5ed | 4f32029c29eee254d9c97045d06bcdf5
(6 rows)
I did it based on
I need 2 random people with every 3 random values
not for previous operators
source to share
select *
from
(
select gen_random_uuid() as person
from generate_series(1,2)
) p
inner join lateral
(
select gen_random_uuid() as utility, p.person
from generate_series(1,3)
) u using (person)
;
person | utility
--------------------------------------+--------------------------------------
d8655c1d-5051-4f5c-929e-efb92a2e6a93 | 1524b662-f2a3-4bfa-bc47-71b96a8e6121
d8655c1d-5051-4f5c-929e-efb92a2e6a93 | e38190f4-bf36-49f9-92c0-11fa18113aad
d8655c1d-5051-4f5c-929e-efb92a2e6a93 | 799a9e39-f954-4d35-8b9a-1fe444d91ccf
3f581430-75be-4c07-aff1-e1c2c802b463 | 3261f4f9-4c28-4709-9b38-cf3ce287317e
3f581430-75be-4c07-aff1-e1c2c802b463 | f9bbadaf-7c68-48f2-bb9b-d57e2902e3f1
3f581430-75be-4c07-aff1-e1c2c802b463 | 158b8e0d-5b22-4f4d-8052-589aaa224b2e
source to share