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?
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.
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 dbfiddle.uk/…