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

On 05/07/14 15:48, Gavin Flower wrote:
> 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
Hmm...

for a 10 times larger range
     id       int PRIMARY KEY default(1000000 + (nextval('rseq') * 
543537 + 997) % 9000000),
also works!