Postgres: insert random boolean value into field?

Question

I am working in Postgres 9.4. I have a table with a boolean field called include_in_rct, and with about 5000 rows. I would like to randomly assign this value as true or false on all existing rows, with a 50% probability in either direction. Can I do this in Postgres?

I need to do something like:

UPDATE mytable SET include_in_rct(<random boolean>)

Or I could set the value to False for every row, then do something like this:

UPDATE mytable SET include_in_rct(FALSE)
UPDATE mytable SET include_in_rct(TRUE) WHERE id IN <some random set of 50% of IDs>

What's the best approach?

Answer 1

I can think of two ways:

First using random()

update mytable
  set include_in_rct = random() > 0.5;

This will not necessarily have an exact 50% distribution.

To get an exact 50/50 distribution you can use:

update mytable
  set include_in_rct = (rn % 2 = 0)
from (
  select id, row_number() over ( order by random() ) as rn
  from mytable
) x
where x.id = mytable.id;

This assumes that id is the primary key of that table.

The first one will however be faster, but for only 5000 rows it won't matter.