How did this statement update 3 rows in Postgres?

I am curious how this statement updated 3 rows in Postgres. All the other times I ran it, it would update 0 or 1. Is there a way to find out which rows?

bestsales=# update keyword set revenue = random()*10 where id = cast(random()*99999 as int);
UPDATE 3

id is the primary key.

id               | integer                        | not null default nextval('keyword_id_seq'::regclass)
    "keyword_pkey" PRIMARY KEY, btree (id)

I tried to run it as SELECT:

bestsales=# select * from keyword where id = cast(random()*99999 as int);
  id   |       keyword       | seed_id | source | search_count | country | language | volume | cpc  | competition | modified_on | google_violation | revenue | bing_violation
-------+---------------------+---------+--------+--------------+---------+----------+--------+------+-------------+-------------+------------------+---------+----------------
  6833 | vizio m190mv        |         | GOOGLE |            0 |         |          |     70 | 0.38 |        0.90 |             |                  |         |
 65765 | shiatsu massage mat |         | SPYFU  |            0 |         |          |    110 | 0.69 |             |             |                  |         |
 87998 | granary flour       |         | SPYFU  |            0 |         |          |     40 | 0.04 |             |             |                  |         |
(3 rows)

And sometimes it would return more than one. How is that possible?

PostgreSQL 9.5.3

It seems that random() is executed once per row and not once for all the statement. So each id value is checked against a different random value. You may get 0, 1, 2 or more, even all the rows of the table, although that possibility would be very small even with 10 rows.

If you want it to be executed once and thus get only one value and update only zero or one rows, you could use a CTE, which are known to be evaluated before the main query:

with rnd as
  ( select random() as random )               -- get just one value
select * 
from keyword, rnd 
where id = cast(rnd.random * 99999 as int) ;  -- and use it everywhere

and the same for the UPDATE:

with rnd as
  ( select random() as random ) 
update keyword
set revenue = random()*10                    -- different random value
from rnd 
where id = cast(rnd.random * 99999 as int);

StackExchange Database Administrators Q#148858, answer score: 5