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

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@postnewspapers.com.au
>
> 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