Thread: Using random() in update produces same random value for all
Hi,
i am trying to update a table with some random dates but that does not seem to work.
UPDATE table
SET last_update=now()::date-(SELECT (random() * 5)::INTEGER + 1)
The updated field is always set to the same. Is there a way to make it random for every record?
I could run it through a function but I wonder if there is s simpler way.
Thanks for any help on this
Alex
On Sun, Jan 14, 2018 at 2:01 AM, Alex Magnum <magnum11200@gmail.com> wrote:
Hi,i am trying to update a table with some random dates but that does not seem to work.UPDATE tableSET last_update=now()::date-(SELECT (random() * 5)::INTEGER + 1) The updated field is always set to the same. Is there a way to make it random for every record?I could run it through a function but I wonder if there is s simpler way.
I verified this with a SELECT, not an UPDATE, but I think leaving this as a simple expression will do what you want. Just leave out the SELECT:
UPDATE table
SET last_update=now()::date-((random() * 5)::INTEGER + 1)
Cheers,
Ken
--
AGENCY Software
A Free Software data system
By and for non-profits
(253) 245-3801
learn more about AGENCY or
follow the discussion.
Yep, interesting. Checked with PostgreSQL 10.1. => select *,random() from generate_series(1,10); generate_series | random -----------------+------------------- 1 | 0.308531506918371 2 | 0.126279713585973 3 | 0.984668150078505 4 | 0.884970095474273 5 | 0.692738385871053 6 | 0.290897831786424 7 | 0.914066118188202 8 | 0.031909613404423 9 | 0.574441066011786 10 | 0.631192437838763 (10 rows) => select *,(select * from random()) from generate_series(1,10); generate_series | random -----------------+-------------------- 1 | 0.0718352268449962 2 | 0.0718352268449962 3 | 0.0718352268449962 4 | 0.0718352268449962 5 | 0.0718352268449962 6 | 0.0718352268449962 7 | 0.0718352268449962 8 | 0.0718352268449962 9 | 0.0718352268449962 10 | 0.0718352268449962 (10 rows) => select *,(select random()) from generate_series(1,10); generate_series | random -----------------+------------------- 1 | 0.848611807450652 2 | 0.848611807450652 3 | 0.848611807450652 4 | 0.848611807450652 5 | 0.848611807450652 6 | 0.848611807450652 7 | 0.848611807450652 8 | 0.848611807450652 9 | 0.848611807450652 10 | 0.848611807450652 (10 rows) Looked like random() is "volatile", but in subselect it works like "stable".
Olleg Samoylov <splarv@ya.ru> writes:
> Looked like random() is "volatile", but in subselect it works like "stable".
The point here is that that's an uncorrelated subselect --- ie, it
contains no outer references --- so it need not be, and is not,
re-evaluated at every outer row.
regards, tom lane
On Mon, Jan 22, 2018 at 9:16 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Olleg Samoylov <splarv@ya.ru> writes:
> Looked like random() is "volatile", but in subselect it works like "stable".
The point here is that that's an uncorrelated subselect --- ie, it
contains no outer references --- so it need not be, and is not,
re-evaluated at every outer row.
That seems rather circular. Why shouldn't a volatile be honored as volatile just because it is in an uncorrelated sub-select?
Cheers,
Jeff
Jeff Janes <jeff.janes@gmail.com> writes:
> On Mon, Jan 22, 2018 at 9:16 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> The point here is that that's an uncorrelated subselect --- ie, it
>> contains no outer references --- so it need not be, and is not,
>> re-evaluated at every outer row.
> That seems rather circular. Why shouldn't a volatile be honored as
> volatile just because it is in an uncorrelated sub-select?
It is honored as volatile: it will be re-evaluated every time the
sub-select is re-evaluated. It's just that there's no cause to
re-evaluate the sub-select.
I poked through the SQL standard to see if it spells out the semantics
of uncorrelated subqueries anywhere, and couldn't find anything relevant
at all. But this is how Postgres has understood the behavior of
sub-selects for a very long time (~20 years). I'm pretty certain
that there are people depending on it to behave this way.
regards, tom lane
On 2018-01-22 23:15, Tom Lane wrote: > > It is honored as volatile: it will be re-evaluated every time the > sub-select is re-evaluated. It's just that there's no cause to > re-evaluate the sub-select. > > I poked through the SQL standard to see if it spells out the semantics > of uncorrelated subqueries anywhere, and couldn't find anything relevant > at all. But this is how Postgres has understood the behavior of > sub-selects for a very long time (~20 years). I'm pretty certain > that there are people depending on it to behave this way. > > regards, tom lane The cause exists, the function is volatile and according to definition it must be recalculated every time. But well, one more example. => select generate_series,(select random+generate_series from random()) from generate_series(1,10); generate_series | ?column? -----------------+------------------ 1 | 1.94367738347501 2 | 2.94367738347501 3 | 3.94367738347501 4 | 4.94367738347501 5 | 5.94367738347501 6 | 6.94367738347501 7 | 7.94367738347501 8 | 8.94367738347501 9 | 9.94367738347501 10 | 10.943677383475 (10 rows) As you can see, sub-select is indeed recalculated, but not random(). And this is may be right, because random() is used as source off data. Another example. => select generate_series,(select random()+generate_series) from generate_series(1,10); generate_series | ?column? -----------------+------------------ 1 | 1.37678202055395 2 | 2.5316761219874 3 | 3.33511888468638 4 | 4.0293406387791 5 | 5.69305071979761 6 | 6.33374964864925 7 | 7.14478175388649 8 | 8.1831739502959 9 | 9.4472619513981 10 | 10.2977624684572 (10 rows) Here random() is recalculated as sub-select. But in => select *,(select random()) from generate_series(1,10); generate_series | random -----------------+------------------- 1 | 0.487761380150914 2 | 0.487761380150914 3 | 0.487761380150914 4 | 0.487761380150914 5 | 0.487761380150914 6 | 0.487761380150914 7 | 0.487761380150914 8 | 0.487761380150914 9 | 0.487761380150914 10 | 0.487761380150914 (10 rows) is not. IMHO all this behavior may be not bad, but it must be well documented in manual in section about sub-selects. All sub-select must be documented as "stable" in terms of function definition. And thus will not be surprise.