Postgres random number by factor of 10

user_4197710 0
Viewed 44

I need a way to generate a random number where I specify the size (size in number of zeros). ie.

  • numbers 0-9 > no zeros
  • numbers 10-99 > one zero
  • numbers 100-999 > two zeros
  • ...
random postgresql
1 Answers

You can use this operation. It multiplies random() by the amount (* 10) to move the decimal up and then discards the decimals.

select trunc(random() * concat('1', repeat('0', numberOfZeros + 1))::int);

or maybe store it in a function

create function randomPositiveNumberByFactor(zeros int)
returns int language plpgsql as $$
BEGIN
    return trunc(random() * concat('1', repeat('0', zeros + 1))::int);
END
$$;

A few examples
select randomPositiveNumberByFactor(0);: numbers 0-9
select randomPositiveNumberByFactor(1);: numbers 10-99
select randomPositiveNumberByFactor(2);: numbers 100-999

user_41977100
Related