Random assigning of numbers in Postgres

Question

I'm mapping a employee number to a 13 digit card number. Card number should be in a range between 9353500001234 and 9353500501234.

Is there any inbuilt function to map a random number from this range to a employee number. Ex employee number 5001 should be assigned to 9353500500934? Also once assigned, is it possible to pass the employee number to a function to get the corresponding card number assigned?

• Craig Ringer: So you want a bi-directional mapping, where one number encodes another and you can get from one to the other without any lookup table? But one number should be pseudorandom-looking? Or is it OK to use a mapping table here?
• Harinath Arasu: It's okay to have a lookup table

Answer 1

Although, in theory, one could generate a bijective function between the domains 1..500000 and 9353500001234...9353500501234, in practice, the easiest way is just to create a map (in the mathematical sense), and SELECT from it.

That is, you would start by creating a table to store pairs of (employee_id, card_number):

CREATE TABLE employee_card
(
 employee_id INTEGER NOT NULL PRIMARY KEY,
 card_number BIGINT NOT NULL UNIQUE 
) ;

Given that table, we fill it with all possible employee_id and card_number, randomly shuffling the card_numbers. We do that by means of the generate_series set returning function, together with row_number(), using a random()ly sorted window (over clause):

INSERT INTO employee_card (employee_id, card_number)
SELECT
 row_number() over(order by random()) AS employee_id, employee_card
FROM
 -- generate card_numbers
 generate_series(9353500001234, 9353500501234) AS s(employee_card)

We add some indexes to get data in an optimal way:

-- Indexes to ease retrieving the data from this table
CREATE INDEX idx_employee_card_card_number ON employee_card(card_number, employee_id);
CREATE INDEX idx_employee_card_employee_id ON employee_card(employee_id, card_number);
VACUUM ANALYZE employee_card;

At this point, if you want to have two functions, they are just SELECTs from the previous table:

-- Get card_number given employee_id
CREATE OR REPLACE FUNCTION card_number_from_employee_id(_employee_id integer) 
 RETURNS BIGINT
 IMMUTABLE
 STRICT
 LANGUAGE SQL
AS
$$
 SELECT card_number FROM employee_card WHERE employee_id = _employee_id ;
$$ ;
-- Get employee_id given card_number
CREATE OR REPLACE FUNCTION employee_id_from_card_number(_card_number BIGINT)
 RETURNS INTEGER
 IMMUTABLE
 STRICT
 LANGUAGE SQL
AS
$$
 SELECT employee_id FROM employee_card WHERE card_number = _card_number ;
$$ ;

You can now test a few employee_ids, and retrieve card numbers. The card numbers should look random:

SELECT 
 employee_id, card_number_from_employee_id(employee_id)
FROM
 generate_series(10001, 10010) AS s(employee_id) ;

employee_id | card_number_from_employee_id
----------: | ---------------------------:
 10001 | 9353500008121
 10002 | 9353500281438
 10003 | 9353500124657
 10004 | 9353500369382
 10005 | 9353500238058
 10006 | 9353500205141
 10007 | 9353500297414
 10008 | 9353500286020
 10009 | 9353500219563
 10010 | 9353500443892

And also test the reverse function:

SELECT
 card_number, employee_id_from_card_number(card_number)
FROM
 generate_series(9353500001234 + 1023, 9353500001234 + 1023 + 9) AS s(card_number);

 card_number | employee_id_from_card_number
------------: | ---------------------------:
9353500002257 | 46754
9353500002258 | 25124
9353500002259 | 481462
9353500002260 | 99475
9353500002261 | 173732
9353500002262 | 171043
9353500002263 | 245192
9353500002264 | 418200
9353500002265 | 76374
9353500002266 | 251095

You can check all the setup and functions at dbfiddle here

• Harinath Arasu: in your example you have generated the employee_id also. In my case its available in a table. how to modify the insert statement such that it picks from below table and populate select employee_number from employee I want the below insert statement to insert employee_id from the employee table INSERT INTO employee_card (employee_id, card_number) SELECT row_number() over(order by random()) AS employee_id, employee_card FROM -- generate card_numbers generate_series(9353500001234, 9353500501234) AS s(employee_card)
• Harinath Arasu: update employee set gs1_src_acct_num = e2.new_gs1_src_acct_num from (select e2.*, (row_number() over (order by random()) - 1 + 9353500001234) as new_gs1_src_acct_num from employee e2 ) e2 where e2.member_number = e.member_number; I would use something like above to do a one time update.