Re: Random-looking primary keys in the range 100000..999999

Gavin Flower <GavinFlower@xxxxxxxxxxxxxxxxx> · Sat, 05 Jul 2014 15:48:35 +1200



    On 05/07/14 01:24, Kynn Jones wrote:

    
          I'm looking for a way to implement pseudorandom primary
            keys in the range 100000..999999.

            
            The randomization scheme does not need to be
            cryptographically strong.  As long as it is not easy to
            figure out in a few minutes it's good enough.

            
            My starting point for this is the following earlier message
            to this list:

            
            http://www.postgresql.org/message-id/49F96730.4000706@xxxxxxxxxxxxxxxxxxxxx

            
            The answer given to it here

            
            http://www.postgresql.org/message-id/448163db-cac5-4e99-8c4c-57cbc6f6af78@mm

            
            ...is really cool, but I don't see how to modify it for the
            case where the domain of the permutation has a cardinality
            that is not a power of 2, as it is in my case (cardinality =
            900000).

            
            ---

            
            (In the crypto world there are "format preserving
            encryption" techniques that probably could do what I want to
            do, but their focus on cryptographic strength makes learning
            and implementing them tough going, plus, the performance
            will probably be poor, since high workloads are an asset for
            such crypto applications.  Since cryptographic strength is
            not something I need, I'm trying to find non-crypt-grade
            alternatives.)

            
          Thanks in advance!

          
        kynn

        
    Hi Kynn,

    
    How about  (note that 'payload' could be any set of valid columns):

    
    --
          using a crude Linear Congruential Generator

      -- not very
          random, but does NOT create duplicates

      
      DROP TABLE IF
          EXISTS rtab;

      DROP SEQUENCE
          IF EXISTS rseq;

      
      CREATE
          SEQUENCE rseq;

      
      CREATE TABLE
          rtab

      (

          id      
          int PRIMARY KEY default(100000 + (nextval('rseq') * 543537 +
          997) % 900000),

          payload 
          int NOT NULL

      );

      
      INSERT INTO
          rtab (payload) VALUES (generate_series(1, 100000));

      
      TABLE rtab;

    
    Sample output:

    
          id   | payload 

      --------+---------

       644534
          |       1

       288071
          |       2

       831608
          |       3

       475145
          |       4

       118682
          |       5

       662219
          |       6

       305756
          |       7

       849293
          |       8

       492830
          |       9

       136367 |     
          10

       679904 |     
          11

       323441 |     
          12

       866978 |     
          13

       510515 |     
          14

       154052 |     
          15

       697589 |     
          16

       341126 |     
          17

       884663 |     
          18

       528200 |     
          19

       171737 |     
          20

    
    Cheers,

    Gavin