Update every row with a random datetime between two dates

Question

I have a column called date_created and I want each row to hold a random date with a date margin of -2 days from the current time.

I am running the below query but it updates all the rows with the same random date. I want every row to be random and not the same.

update table set date_created=(select NOW() + (random() * (NOW()+'-2 days' - NOW())) + '-2 days')

Any idea would be appreciated.

Answer 1

Use an expression in place of a query:

update my_table 
set date_created= NOW() + (random() * (NOW()+'-2 days' - NOW())) + '-2 days'

Answer 2

PostgreSQL is optimizing your subquery so that it's only run once, causing the same random value to be used for all rows. To ensure random() is run once for each row, use an expression instead of a subquery. Also, your calculation can be simplified a bit.

Suggested improved query:

UPDATE my_table SET date_created = now() - random() * INTERVAL '2 days'