Postgres Randomized Primary Keys

There are times in your life when you might be asked to randomize the primary keys of a table so that they are numeric, unique, but non-sequential. If you been assigned this task, then this post is for you ❤.

Numeric, Unique, but Non-Sequential?

Enter the Feistel Cipher

What About My Existing Records?

The Postgres Custom Functions

CREATE FUNCTION pseudo_encrypt_24(VALUE int) returns int AS $$
DECLARE
l1 int;
l2 int;
r1 int;
r2 int;
i int:=0;
BEGIN
l1:= (VALUE >> 12) & (4096-1);
r1:= VALUE & (4096-1);
WHILE i < 3 LOOP
l2 := r1;
r2 := l1 # ((((1366 * r1 + 150889) % 714025) / 714025.0) * (4096-1))::int;
l1 := l2;
r1 := r2;
i := i + 1;
END LOOP;
RETURN ((l1 << 12) + r1);
END;
$$ LANGUAGE plpgsql strict immutable;
CREATE FUNCTION bounded_pseudo_encrypt(VALUE int, MAX int, MIN int) returns int AS $$
BEGIN
LOOP
VALUE := pseudo_encrypt_24(VALUE);
EXIT WHEN VALUE <= MAX AND VALUE >= MIN;
END LOOP;
RETURN VALUE;
END
$$ LANGUAGE plpgsql strict immutable;
DROP FUNCTION bounded_pseudo_encrypt(VALUE int, MAX int, MIN int);
DROP FUNCTION pseudo_encrypt_24(VALUE int);

Ensuring They Work

-- Returns 50 rows
SELECT n, bounded_pseudo_encrypt(n, 50000, 10000) FROM generate_series(1,50) n;
-- Returns a single row
select bounded_pseudo_encrypt(1, 16777215, 2);
Notice how the bounded_pseudo_encrypt values are respectful of our max (50k) and min (10k) values. Running this query again should result in the exact same results, ensuring we never have any duplicate values.

Updating Your Primary Key Column

-- Assuming you already have 1000 existing records and want the max (16,777,215) number of IDs...ALTER TABLE your_table ALTER COLUMN id SET DEFAULT bounded_pseudo_encrypt(nextval('your_table_your_table_id_seq')::int, 16777215, 1000);

That’s All Folks!

Situated at @closingfolders as the VP of Engineering. I enjoy most things involved with the internet, up and down the stack.