3

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.

Bugs
  • 4,491
  • 9
  • 32
  • 41
user1411837
  • 1,494
  • 6
  • 27
  • 41

2 Answers2

6

Use an expression in place of a query:

update my_table 
set date_created= NOW() + (random() * (NOW()+'-2 days' - NOW())) + '-2 days'
klin
  • 112,967
  • 15
  • 204
  • 232
  • 1
    It seems a good solution, but it would be better with two lines on explanation...for example on how to define / use min & max values! Thx – funder7 Dec 02 '20 at 23:08
5

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'
markusk
  • 6,477
  • 34
  • 39
  • Hey @markusk, maybe just tell the user why your answer is better than what he has. I expect that this may not always produce a unique value. now(), which is basically random - a random number, may produce something that has been generated before. (10 - 2) == (11-3) – Anthony Horne Aug 02 '17 at 10:46
  • @anthony-horne The original question does not require all values to be unique, just that they don't all get the exact same value. As pointed out by klin, this was caused by using a subquery to create the value. Using an expression instead ensures a new random value will be used for each row, and klin's answer was accepted. My answer only differs from the currently accepted answer in that it's shorter and easier to read. – markusk Aug 02 '17 at 10:53
  • @anthony-horne Elaborated somewhat. – markusk Aug 02 '17 at 11:07