Postgresql update each row with a random value

Question

This is similar to other questions but it appears to vary on a database-by-database implementation. I am using postgres and things like NEWID do not appear to exist.

This is what I want to work:

update foo set bar = (select floor(random() * 10) + 1);

I understand why it doesn't but I can't seem to find a suitable solution that works for Postgres. I want the value of bar to be a random number between 1 and 10 that differs per row.

Eugen Konkov · Accepted Answer · 2018-11-07 11:35:05Z

60

I think that Postgres might optimize the subquery. Doesn't this work?

update foo
   set bar = floor(random() * 9 + 1);  -- from 1 to 10, inclusive

edited Nov 7, 2018 at 11:35

Eugen Konkov

26k17 gold badges128 silver badges190 bronze badges

answered Apr 6, 2016 at 22:10

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Doogle Over a year ago

my take is to create a random function and use it.

CREATE OR REPLACE FUNCTION random_between(low INT ,high INT)     RETURNS INT AS$$ BEGIN    RETURN floor(random()* (high-low + 1) + low); END; $$ language 'plpgsql' STRICT;

Then use the function update foo set bar = random_between(1,10)

Eugen Konkov Over a year ago

@Doogle: this is not required to +1

Alexey K · Accepted Answer · 2020-09-11 18:28:18Z

17

update foo set bar = floor(random() * 10) + 1;

edited Sep 11, 2020 at 18:28

answered Oct 22, 2018 at 13:11

Alexey K

3153 silver badges8 bronze badges

2 Comments

jorisw Over a year ago

This was very helpful in anonymizing my accounts table's emails:

UPDATE accounts SET email = (select 'anonymized-' || (select floor(random() * 1000000) + 1 where accounts.id = accounts.id) || '@localhost')

Alexey K Over a year ago

updated. there is no need in "where accounts.id = accounts.id". just call function directly. without 'select'.

Collectives™ on Stack Overflow

Postgresql update each row with a random value

2 Answers 2

2 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related