Return SQL random numbers not in table

Question

I have a table with user_ids that we collected from the streaming data source of active accounts. Now I am looking to go through and fill out information about user_ids that do nothing.

Is there any SQL (postgres, if that matters) way to return a query to random numbers not in the table?

For example, something like this:

SELECT RANDOM(count, lower_bound, upper_bound) as new_id 
WHERE new_id NOT IN (SELECT user_id FROM user_table) AS user_id_table

Perhaps, or it would be better to create a bunch of random numbers using the script shell and pass them to the database to determine nonexistent?

Answer 1

It is possible. If you want identifiers to be integers, try:

SELECT trunc((random() * (upper_bound - lower_bound)) + lower_bound) AS new_id 
FROM generate_series(1,upper_bound) 
WHERE new_id NOT IN (
    SELECT user_id 
    FROM user_table)

Answer 2

,

SELECT * FROM (SELECT trunc(random() * (upper - lower) + lower) AS new_id  
FROM generate_series(1, count)) AS x 
WHERE x.new_id NOT IN (SELECT user_id FROM user_table)

Answer 3

, . - :

SELECT s
  FROM generate_series(1, (select max(user_id) from users) s
  LEFT JOIN users ON s.s = user_id
 WHERE user_id IS NULL
 order by random() limit 5;

, . , , , , , , .

Answer 4

, , , .

, :

CREATE TABLE test (a int)

0 4 (random() * 5)::int, .

 WITH RECURSIVE rand (i, r, is_new) AS (
  SELECT 
     0,
     null,
     false
  UNION ALL
    SELECT 
      i + 1,
      next_number.v,
      NOT EXISTS (SELECT 1 FROM test WHERE test.a = next_number.v) 
   FROM
     rand r,
     (VALUES ((random() * 5)::int)) next_number(v)
   -- safety check to make sure we do not go into an infinite loop
   WHERE i < 500
)
SELECT * FROM rand WHERE rand.is_new LIMIT 1

I'm not super sure, but PostgreSQL should be able to stop iterating when it has one result, since it knows that the query has a limit of 1.

The good thing about this query is that you can replace (random() * 5)::intwith any identifier generation function you want

Answer 5

My pragmatic approach: create 500 random numbers, and then select one that is not in the table:

WITH fivehundredrandoms AS ( RANDOM(count, lower_bound, upper_bound) AS onerandom
FROM (SELECT generate_series(1,500)) AS fivehundred )
SELECT onerandom FROM fivehundredrandoms 
WHERE onerandom NOT IN (SELECT user_id FROM user_table WHERE user_id > 0) LIMIT 1;