Postgresql - Inserting dummy data into Postgres table

Westcoaster
October 6, 2023
212 views
0 votes
2 Answers

I have got the following Postgres table:

create table test (
    id serial, 
    contract varchar, 
    amount0 int, 
    amount1 int, 
    price double precision
);

I would like to insert 100 rows of dummy data that conforms to the following:

– In column ‘contract’ there should be values out of ‘abc’, ‘klm’ and ‘xyz’.

– In columns ‘amount0’ and ‘amount1’ there should be integer values out of 50, 60, 80, 100, 200.

– In column ‘price’ there should be values out of 1.5, 1.8, 2.1, 2.5.

What I have come up with so far is this:

INSERT INTO test (amount0, amount1, price)
SELECT
  (SELECT val FROM unnest(ARRAY[50, 60, 80, 100, 200]) AS val ORDER BY random() LIMIT 1),
  (SELECT val FROM unnest(ARRAY[50, 60, 80, 100, 200]) AS val ORDER BY random() LIMIT 1),
  (SELECT val FROM unnest(ARRAY[1.5, 1.8, 2.1, 2.5]) AS val ORDER BY random() LIMIT 1)
FROM generate_series(1, 100);

But this doesn’t do the trick. This statement always only uses ‘100’ for column ‘amount0′, ’50’ for column ‘amount1’ and ‘2.1’ for column ‘price’.

Can anyone help me out?

Answers

- UmutTEKN
- October 6, 2023 at 5:14 pm
- 0 votes
0
The problem is everytime you run your command PostgreSQL took a seed number and send it to srand function. That is why with the same seed you get same values from your arrays. Therefore, you need call function as many times you want another value from your arrays, and you can do that creating functions. That will cause context switch, but that is the only thing that I can think of. Maybe someone else can correct me. Here is the code:
```
create table test (
    id serial, 
    contract varchar, 
    amount0 int, 
    amount1 int, 
    price double precision
);

CREATE OR REPLACE FUNCTION random_amount()
  RETURNS int
  LANGUAGE sql VOLATILE PARALLEL SAFE AS
$func$
  SELECT ('[0:4]={50, 60, 80, 100, 200}'::int[])[trunc(random()  * (4 - 1 + 1) + 1)::int];
$func$;

CREATE OR REPLACE FUNCTION random_price()
  RETURNS int
  LANGUAGE sql VOLATILE PARALLEL SAFE AS
$func$
  (SELECT ('[0:3]={1.5, 1.8, 2.1, 2.5}'::float[])[trunc(random()  * (3 - 1 + 1) + 1)::int]);
$func$;

INSERT INTO test (amount0, amount1, price)
SELECT
  random_amount(), random_amount(), random_price()
FROM generate_series(1, 100);

select * from test;
```
Fiddle.
Login or Signup to reply.

- Zegarek
- October 6, 2023 at 7:04 pm
- 0 votes
0
You can randomly subscript the array of choices: (demo)
```
INSERT INTO test (amount0, amount1, price)
SELECT (ARRAY[50, 60, 80, 100, 200])[(random()*4+1)::int],
       (ARRAY[50, 60, 80, 100, 200])[(random()*4+1)::int],
       (ARRAY[1.5, 1.8, 2.1, 2.5]  )[(random()*3+1)::int]
FROM generate_series(1, 1e2, 1);

SELECT * FROM test LIMIT 6;
```
id amount0 amount1 price

1 200 60 1.5

2 80 100 1.8

3 60 80 2.1

4 100 50 2.1

5 100 80 1.8

6 60 200 1.8

Note that this does what you intended to do, making each selection independently random. In effect, it’s quite likely that you’ll get duplicate entries. If you prefer to keep getting unique combinations until you run out, and only then start over, you can do something similar to Mike’s suggestion: (demo2)
```
INSERT INTO test (amount0, amount1, price)
SELECT            amount0, amount1, price
FROM (values (50),(60),(80),(100),(200)) a(amount0),
     (values (50),(60),(80),(100),(200)) b(amount1),
     (values (1.5), (1.8), (2.1), (2.5)) c(price),
     generate_series(1, 1e2, 1) duplicator(n)
ORDER BY n, random()
LIMIT 100;
```
Here’s a function if you find yourself in need of a weighted random, meaning that you want to define exactly how likely an option is, compared to the others.

The reason why your solution didn’t work is that since your scalar subqueries don’t depend in any way on the outer query, they were evaluated only once and re-used. You can check the plan by running it with explain analyze verbose. You could trick the planner into thinking they somehow rely on the outer query by adding an outside reference, even if it does nothing (demo3) but the above does the same, in less code.
Login or Signup to reply.