How to generate random data on an associated table in postgres?

Question

I have two tables: users and results. A user has many results.

I need to generate 10 million records in our users table and I was able to do this using the generate_series function in postgres.

Now, I want to generate millions of rows in the results table. But I want there to be a certain distribution of these rows. For example, I want 50% of the users to only have 1 result. I want 40% to have 2 results. And I want 10% to have 5 results.

Is there a way to generate this random data in the results table in postgres?

Gordon Linoff · Accepted Answer · 2021-07-14 19:44:57Z

1

Yes:

select u.user_id, gs.result
from (select u.*,
             ntile(10) over (order by rand()) as decile
      from users u
     ) u cross join lateral
     generate_series(1, (case when u.decile <= 5 then 1 when u.decile <= 9 then 2 else 5 end)) gs(result);

This generates the rows. You can fill in with the data you want.

edited Jul 14, 2021 at 19:44

answered Jul 14, 2021 at 15:21

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

user402516 Over a year ago

this is really awesome, thank you. This is much more advanced sql than I know so a question for you: how does generate_series know to go through every "decile" value and generate it's resulting table? Does it know that decile holds values 1-10 and does something under the hood?

Gordon Linoff Over a year ago

@user402516 . . . ntile() returns a value between 1 and 10 (because 10 is the argument). I added table aliases to make it clear that generate_series() is using this information.

user402516 Over a year ago

I noticed you added the "lateral" to the "Cross join" your query above. But the query seemed to work without it. From googling, it seems that you need lateral there in order to reference subquery aliases. But that didn't seem to be the case as the original query worked for me. Did you change it just to be more explicit?

Gordon Linoff Over a year ago

@user402516 . . . The lateral is optional for generate_series(). I try to always include it so the logic is quite clear.

Collectives™ on Stack Overflow

How to generate random data on an associated table in postgres?

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related