Generate a random number of non duplicated random number in [0, 1001] through a loop

Question

I need to generate a random number of non duplicated random number in plpgsql. The non duplicated number shall fall in the range of [1,1001]. However, the code generates number exceeding 1001.

directed2number := trunc(Random()*7+1);
counter := directed2number
while counter > 0
loop
to_point := trunc((random() * 1/directed2number - counter/directed2number + 1) * 1001 +1);
...
...
counter := counter - 1;
end loop;

Answer 1

If I understand right

• You need a random number (1 to 8) of random numbers.
• The random numbers span 1 to 1001.
• The random numbers need to be unique. None shall appear more than once.

CREATE OR REPLACE FUNCTION x.unique_rand_1001()
RETURNS SETOF integer AS
$body$
DECLARE
    nrnr    int := trunc(random()*7+1);  -- number of numbers
BEGIN

    RETURN QUERY
    SELECT (1000 * random())::integer + 1
    FROM   generate_series(1, nrnr*2)
    GROUP  BY 1
    LIMIT  nrnr;

END;
$body$ LANGUAGE plpgsql VOLATILE;

Call:

SELECT x.unique_rand_1001();

Numbers are made unique by the GROUP BY. I generate twice as many numbers as needed to provide enough numbers in case duplicates are removed. With the given dimensions of the task (max. 8 of 1001 numbers) it is astronomically unlikely that not enough numbers remain. Worst case scenario: viewer numbers are returned.

Answer 2

I wouldn't approach the problem that way in PostgreSQL.

From a software engineering point of view, I think I'd separate generating a random integer between x and y, generating 'n' of those integers, and guaranteeing the result is a set.

-- Returns a random integer in the interval [n, m].
-- Not rigorously tested. For rigorous testing, see Knuth, TAOCP vol 2.
CREATE OR REPLACE FUNCTION random_integer(integer, integer)
  RETURNS integer AS
$BODY$
   select cast(floor(random()*($2 - $1 +1)) + $1 as integer);
$BODY$
  LANGUAGE sql VOLATILE

Then to select a single random integer between 1 and 1000,

select random_integer(1, 1000);

To select 100 random integers between 1 and 1000,

select random_integer(1, 1000)
from generate_series(1,100);

You can guarantee uniqueness in either application code or in the database. Ruby implements a Set class. Other languages have similar capabilities under various names.

One way to do this in the database uses a local temporary table. Erwin's right about the need to generate more integers than you need, to compensate for the removal of duplicates. This code generates 20, and selects the first 8 rows in the order they were inserted.

create local temp table unique_integers (
    id serial primary key,
    n integer unique
);

insert into unique_integers (n)
select random_integer(1, 1001) n
from generate_series(1, 20)
on conflict (n) do nothing;

select n 
from unique_integers
order by id
fetch first 8 rows only;