inserting 1 million random data into PostgreSQL

Question

the table structure is like this:

milliontable(
   name varchar(10),
   age integer,
   joindate date
)

and I want to insert random 1 million data into that table. Is there any way to do that?

Answer 1

Use the random() function to generate random values:

INSERT INTO milliontable (name, age, joindate)
SELECT substr(md5(random()::text), 1, 10),
       (random() * 70 + 10)::integer,
       DATE '2018-01-01' + (random() * 700)::integer
FROM generate_series(1, 1000000);

It is usually a silly idea to store the age of a person in a table, as this number becomes wrong automatically as time goes by. Use the birthday.

Answer 2

You can use recursive queries. First is to generate rows from 1 to 1M. then use random() function to generate random age between 1 to 99 years old.

with recursive cte as (
   select 0 as ctr
   union all 
   select ctr + 1 from cte where ctr < 1000000
)
insert into milliontable (name, age, joindate) 
select 'name'||cast(ctr as varchar(30)) as name, floor(random()*(99)) as age, current_timestamp as joindate 
from cte;

try dbfiddle.