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

many ways to do it. Loop for 10000, in the loop make a dummy object(operation) with data then single insert with JDBC. Thats it. Or make 10000 objects then loop to insert those! — Nur Zico
– Nur Zico, Commented Aug 7, 2017 at 11:19

Laposhasú Acsa · Accepted Answer · 2017-08-07 12:22:38Z

31

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)

edited Aug 7, 2017 at 12:22

answered Aug 7, 2017 at 12:12

Laposhasú Acsa

1,59017 silver badges18 bronze badges

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

2 Comments

M. Wojcik Over a year ago

Thanks man you are the best! Two more question if u don't mind, from your query I get date format like: 2017-08-07T12:30:37.400894Z, what if I only want: 2017-08-07T12:30:37? And you said I can access the values that generate_series generates. How can i do it?

Laposhasú Acsa Over a year ago

You can use date_trunc for truncate a timestamp to a given precision. If you assign an alias for generate_series, you can use that in your SELECT list. For example SELECT a FROM generate_series(1,10) AS a;

Collectives™ on Stack Overflow

PostgreSQL loop with random inserts

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related