PostgreSQL loop with random inserts

Question

I have a table in db:

CREATE TABLE operation ( <br>
    id     integer NOT NULL DEFAULT NEXTVAL ('seq_operation'),( <br>
    phone_number     varchar(30),( <br>
    age     integer,( <br>
    gender     char(1),( <br>
    isActive     boolean,( <br>
    date_of_surgery     timestamp,( <br>
);

I need to insert 10000 rows with random data. How can I make such INSERT statement? Im fresh with this stuff and trying to figure it out with other answers for similiar question here, but cant find easy understandable one for me.

I would really appreciate your help.

Best regards, Max

Answer 1

I usually use something like this is psql:

INSERT INTO table (values, to, fill) 
SELECT random(), random(), random() from generate_series(1,10000);

In your case, this will be:

INSERT INTO operation ( 
  phone_number,
  age,
  gender,
  isActive,
  date_of_surgery
) SELECT 
  'some-phone-' || round(random()*1000), -- for text
  round(random()*70), -- for integer
  (ARRAY['f','m'])[round(random())+1], -- for char/enum
  (ARRAY[false,true])[round(random())+1], -- for boolean
  now() + round(random()*1000) * '1 second'::interval -- for timestamps
FROM generate_series(1,10000);

A bit more explanation.

• generate_series will provide you the loop, also you can access the values it generates. Those are not needed now.

• 'text' || round(random()*1000) can generate 'text-1212'-like unique strings.

• round(random()*70) - you need to round, because random() returns a floting point value between 0 and 1.

• (ARRAY['f','m'])[round(random())+1] - for enum and alike, build an array and generate a random index for it

• now() + round(random()*1000) * '1 second'::interval - get a baseline date and add random time intervals.

(fiddle)