Results 1 to 2 of 2
  1. #1
    Join Date
    Apr 2003

    Unanswered: generate random numbers


    I want to generate random number as activation_code in my system_users table but the number must be between 1000000000 and 9999999999 and MUST NOT exist in the table.

    How can I do that?


  2. #2
    Join Date
    Nov 2003
    Provided Answers: 23
    Something like this might work:
    SELECT new_code
    FROM (
      SELECT cast( (random() * 9999999999) + 1000000000 as bigint) AS new_code
      FROM generate_series(1,1000)
    ) t 
    WHERE new_code NOT IN (SELECT activation_code FROM system_users)
    LIMIT 1;
    When the table grows, you'll probably need to increase the number of random values generated in the inner select in order to get at least one "free" number

    But you will need to exclusively lock the entire table before doing this, otherwise you will run into trouble when this is executed from different connections (i.e. in different transactions)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts