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.4000...@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!