Use fixed list as random values in SQL query

Question

I have a table into which I want to generate test data:

INSERT into onboarding_tasks (business_name, created_at, status)
SELECT
    'Business name ' || id AS business_name,
    (NOW() + (random() * (NOW() + '90 days' - NOW())) + '30 days') AS created_at,
    /* here I can have values NEW, IN_PROGRESS, COMPLETED */ AS status,    
FROM generate_series(1,25) as g(id);

Do you know how I can implement a logic into the SQL query to generate random values from a fixed options?

Maybe this can help: https://stackoverflow.com/questions/14299043/postgresql-pl-pgsql-random-value-from-array-of-values — Reinis Verbelis, Oct 17 '21 at 14:06

score 0 · Answer 1 · answered Oct 17 '21 at 14:57

You can combine CASE with the RANDOM() function to achieve this.

For example:

SELECT
    'Business name ' || id AS business_name,
    (NOW() + (random() * (NOW() + '90 days' - NOW())) + '30 days') AS created_at,
    case when random() < 0.3333 then 'NEW'
         when random() < 0.5 then 'IN_PROGESS'
         else 'COMPLETED'
    end AS status
FROM generate_series(1,10) as g(id);

Result:

 business_name     created_at                     status     
 ----------------- ------------------------------ ---------- 
 Business name 1   2021-12-03 07:37:08.519843+00  NEW        
 Business name 2   2022-01-22 19:14:10.085483+00  COMPLETED  
 Business name 3   2021-11-23 16:47:39.207064+00  COMPLETED  
 Business name 4   2022-01-26 13:08:33.643398+00  NEW        
 Business name 5   2021-12-05 03:26:44.730359+00  COMPLETED  
 Business name 6   2022-02-07 07:15:05.470689+00  COMPLETED  
 Business name 7   2022-01-13 05:00:15.326175+00  COMPLETED  
 Business name 8   2022-02-08 02:43:43.123768+00  IN_PROGESS 
 Business name 9   2021-11-17 06:06:40.14872+00   NEW        
 Business name 10  2021-12-19 07:59:55.786655+00  NEW

See running example at db<>fiddle.

Shouldn't the second WHEN condition be `< 0.6666` to get an even distribution? — , Oct 17 '21 at 15:01
@a_horse_with_no_name Interesting. I assumed PostgreSQL would run the `RANDOM()` function again every single time. Your comment points in the opposite direction; i.e. that a single `RANDOM()` function call is reused on the same row. A quick run of `select random(), random()` shows it runs it every time. See https://dbfiddle.uk/?rdbms=postgres_13&fiddle=4e8b3b9c547b3d45fc9ab0957972fd6b — The Impaler, Oct 17 '21 at 15:04

Use fixed list as random values in SQL query

1 Answers1