Generate random date into series

Question

I use this SQL query to generate random data:

INSERT into tasks SELECT generate_series(1,3) AS id, 
md5(random()::text) AS business_name, 
select NOW() + (random() * (NOW()+'90 days' - NOW())) + '30 days' AS created_at,
md5(random()::text) AS meta_title,
md5(random()::text) AS status,
md5(random()::text) AS title,
md5(random()::text) AS type

I get this error:

ERROR:  syntax error at or near "select"
LINE 3: select NOW() + (random() * (NOW()+'90 days' - NOW())) + '30 ...
        ^
SQL state: 42601
Character: 105

What is the proper way to generate random date?

`select now()` should only be `now()` there is no need to prefix function calls with `select` — , Aug 31 '21 at 08:42

score 1 · Answer 1 · answered Aug 31 '21 at 08:43

you could use either of these syntax

INSERT into tasks SELECT generate_series(1,3) AS id, 
md5(random()::text) AS business_name, 
(select NOW() + (random() * (NOW()+'90 days' - NOW())) + '30 days') AS created_at,
-- note that the select is in parenthesis
md5(random()::text) AS meta_title,
md5(random()::text) AS status,
md5(random()::text) AS title,
md5(random()::text) AS type

OR

INSERT into tasks SELECT generate_series(1,3) AS id, 
md5(random()::text) AS business_name, 
NOW() + (random() * (NOW()+'90 days' - NOW())) + '30 days' AS created_at,
--note select removed
md5(random()::text) AS meta_title,
md5(random()::text) AS status,
md5(random()::text) AS title,
md5(random()::text) AS type

Stefanov.sm · Accepted Answer · 2021-08-31T08:59:56.837

1

Fixed the created_at expression

INSERT into tasks
SELECT generate_series(1,3) AS id, 
md5(random()::text) AS business_name, 
now() + random() * interval 'P90DT1H' + '30 days' AS created_at,
md5(random()::text) AS meta_title,
md5(random()::text) AS status,
md5(random()::text) AS title,
md5(random()::text) AS type;

(NOW() + '90 days' - NOW()) is equivalent to interval 'P90DT1H' because of the summer time one hour shift.

edited Aug 31 '21 at 08:59

answered Aug 31 '21 at 08:53

Stefanov.sm

11,215
2
21
21

One additional question: I get now `ERROR: value too long for type character varying(20)` Can I somehow specify only 2 types of string for example `new` or `in_progress` just for `type` column? – Peter Penzov Aug 31 '21 at 09:01
I do not think so. Better always use `text` instead of `character varying(XX)`. This is more convenient and a bit more efficient. Details [here](https://stackoverflow.com/questions/4848964/difference-between-text-and-varchar-character-varying). – Stefanov.sm Aug 31 '21 at 09:03
Is it possible to set String max size when I call `md5(random()::text)`? For example I would like to generate random String with 4 characters. – Peter Penzov Aug 31 '21 at 09:10
Yes, use this expression `left(md5(random()::text), 4)` – Stefanov.sm Aug 31 '21 at 09:11

Generate random date into series

2 Answers2