Postgres: insert random boolean value into field?

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.


Always K.I.S.S.

select round(random());

For the question:

select (round(random())::int)::boolean;

Tags:

Postgresql