PSQL generate aleatory data procedure-CodePudding

I need to create a stored procedure without generate_series in which maybe 100,000 records are inserted into a simple table randomly. I leave an example table so you can tell me how you would do it

create table test(
  id serial primary key,
  name varchar(20),
  int number,
  birth datetime
);

For that table, how would you randomly generate 100,000 records in a procedure? I've been looking for ways for two hours and nothing

CodePudding user response：

A quick and dirty solution. Not particularly efficient. Also not sure what you actually want the data to look like. Still it is a starting point:

create table test(
  id serial primary key,
  name varchar(20),
  number integer,
  birth date
);

DO $$
DECLARE 
    name_val varchar(20);
    number_val integer;
    birth_val date;
BEGIN
    for i in 1..10 LOOP
        name_val = repeat('se', (random() * 10)::int);
        number_val = random() * 1000;
        birth_val = (current_date - (random() * 300)::int) - ((((random() * 10) * 3)::int)::varchar || ' years')::interval;
        INSERT INTO test (name, number, birth) values(name_val, number_val, birth_val);
    END LOOP;
END;
$$
;

select * from test;
 id |         name         | number |   birth    
---- ---------------------- -------- ------------
  1 | sesesesesesesese     |    376 | 1997-02-16
  2 | se                   |    318 | 2009-02-16
  3 | sesesesese           |    518 | 1997-02-16
  4 |                      |    605 | 1993-02-16
  5 | sesesesesesesesese   |    842 | 1998-02-16
  6 | sesesesesesesese     |    219 | 2015-02-16
  7 | sesesese             |      7 | 2017-02-16
  8 | sesesesesesesesesese |    477 | 2014-02-16
  9 | sesesesesese         |    690 | 2013-02-16
 10 | se                   |    796 | 2011-02-16

Did a 1..10 LOOP for demonstration purposes.