Question 246928 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Insert random account numbers with Java code or with database function?

I’ve have started building a banking project using Java, a Spring Boot application, and a PostgreSQL database. I want to generate a random 8 or 10-digit account number each time I create a new account. What’s the best way to achieve this?

Option 1: I can handle this in the Java service layer when creating a new account (but how can I ensure it’s unique?).

Option 2: I can design the database like this:

CREATE TABLE IF NOT EXISTS account (
id SERIAL PRIMARY KEY,
account_number VARCHAR(8) UNIQUE, -- Unique 8-character account number
-- Other columns for account details
);

Then create an SQL function to generate a random number like:

CREATE OR REPLACE FUNCTION generate_random_account_number()
RETURNS VARCHAR(8) AS $$
BEGIN
   RETURN lpad(floor(random() * 100000000)::int::text, 8, '0');
END;
$$ LANGUAGE plpgsql;

Then insert it like:

INSERT INTO account (account_number, other_columns)
VALUES (generate_random_account_number(), 'other_data');

I’m not sure which approach is better to achieve this, and how I can ensure that the account numbers will be unique each time they are created.

PS: I don’t anticipate encountering issues with uniqueness because this is a hobby project, and I won’t be creating more than 100 accounts.

Tags:

3

Answers


  1. 0

    I would go with this way:

    Option 1: I can handle this in the Java service layer when creating a new account

    How can you ensure it’s unique?

    Create the random account number and after creation, execute a database query which returns all datasets with the account number. If the amount of returned results is greater than 0, then create a new random account number and check again. Do this in a loop until the database query does not return any datasets.

    Reasons for this answer are:

    • IMO it is a good style to let the database be a database and let the backend code be handling the business logic. You can create the field in your database table as unique if you want. This will double check your account number
    • This code is easy to write and easy to read. IMO this is the most important thing
    • There will be no performance issues. Yes, there MAY be the chance that your loop will run more than once, but you will never notice this. 9,999,999,999 possibilities are out there; and even if you have one million accounts, this loop will nearly never run more than twice.

    Tipps from an older iron

    • Code-readability first. You will read your code 100 times more than you will write it.
    • Performance last. Performance optimized code often uses maps or ist hard verbose etc. Write simple and intuitive code, and if you run into performance issues, then you can think about optimization.
    • Do not code "super fancy triple ultra generic code" to save 3 lines. This is not cool anymore the next time you need to alter something there.
    • Keep it always simple. The IDE is the place to code. If you want to flex, then you need to go to the club.
    • If you do not unserstand your own code anymore, you are doing something wrong (in most scenarios).
    • The best unit test is the one, that you do not need to write.
    • Be kind and friendly to other programmers and do not evolve to an egoistic "I am the best and all others are stupid" programmer. Even of code looks bad or you think it MUST be stupid – think twice. Someone had his/her own thoughts about it!

    And now grow and be the best Version of yourself!

    Login or Signup to reply.
  2. 0

    I want to generate a random 8 or 10-digit account number each time.

    The first question is why you want that. If that number has to meet specific criteria, list them precisely. Does the number have to be truly random or just arbitrary? Why digits, and why 8 or 10 of those? Leading zeros are ok? Significant? Etc.

    Why the additional surrogate PK id? Isn’t account_number a natural key?
    Why varchar(8) if it’s just digits?

    Think of multiple accounts created concurrently by different clients. The database can avoid conflicts reliably, as opposed to the client.

    Unless there are specific criteria, use a bigint IDENTITY column. An arbitrary, but not random number. Leading zeros are insignificant noise:

    CREATE TEMP TABLE account (
  account_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY
, name text NOT NULL   
  -- other columns for account details
, CONSTRAINT id_max_10_digits CHECK (account_id BETWEEN 1 AND 9999999999)
);

    See:

    10 digits is one too many for plain integer. So bigint.
    I threw in a CHECK constraint to enforce your number range.

    To insert and get back the generated account_id in one step:

    INSERT INTO account (name)
VALUES ('Foo')
RETURNING account_id;

    If you need a 10-digit string format, you can always format the number for presentation. Like to_char(account_id, '0000000000'), or lpad(account_id::text, 10, '0') – which is better because immutable. You can even add a generated column to the table if you must:

    CREATE TABLE account (
  account_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY
, account_nr text GENERATED ALWAYS AS (lpad(account_id::text, 10, '0')) STORED
, name text NOT NULL   
  -- other columns for account details
, CONSTRAINT id_max_10_digits CHECK (account_id BETWEEN 1 AND 9999999999)
);

    See:

    Truly random

    If you need truly random numbers, the inherent weakness is that conflicts are possible in principle.

    CREATE OR REPLACE FUNCTION public.generate_random_account_number()
  RETURNS bigint
  LANGUAGE SQL VOLATILE PARALLEL SAFE AS
$func$
SELECT trunc(random() * 10000000000)::bigint;
$func$;

    Then:

    CREATE TABLE account (
  account_id bigint PRIMARY KEY DEFAULT public.generate_random_account_number()
-- , ... rest like above

    UUID

    To make conflicts practically impossible, use uuid values instead of your 10-digit number. Those can be generated in the client or in the database as you like. They are just a bit unwieldy, and occupy 16 bytes. About the likelihood of conflicts, consider the chapter "Probability of a hash collision?" here:

    Login or Signup to reply.
  3. 0

    You need a function that reliably generates 10-digit numbers from sequential inputs and which is seemingly random, that is, someone not knowing your function will not know which numbers follow the previous. A way to achieve it is to define a pattern. Let’s say that we have a 10-digit multiple of large numbers, like 9754031400

    which is

    12345 × 98765 × 8

    So, we can have

    1 + (98765 * 1), 1 + (98765 * 2), …, 1 + (98765 * 98759)

    as our first set of numbers (multiples of 98765 + 1 below 9754031400). When we run out of numbers in this interval, we can continue the series with adding 2 to the multiples:

    2 + (98765 * 1), 2 + (98765 * 2), …, 2 + (98765 * 98759)

    and so on. Admittedly, this is not obfuscated-enough yet. So we can shuffle the digits. If the digits of the original number were initially

    d1 d2 d3 d4 d5 d6 d7 d8 d9 d10

    then you can change their order for the purpose of obfuscating the results to

    d10 d7 d5 d6 d3 d4 d8 d1 d9 d2

    This would be really difficult to reverse-engineer. But if this is not enough, you can always add further levels of obfuscation. Since this is an algorithmic example and Javascript is the only language we can add as a snippet, I will illustrate this via Javascript, but you can implement this algorithm in Java or SQL without any problems

    const magicNumber = 98765;
const magicLimit = 9754031400;
const period = parseInt(magicLimit / magicNumber) - 1;
const digitNumber = 10;
function obfuscate(input) {
    let offset = 1 + parseInt(input / period);
    let mod = input % period;
    let firstLevel = offset + mod * magicNumber;
    let str = firstLevel + "";
    while (str.length < digitNumber) str = "0" + str;
    let rawArray = str.split("");
    let newDigits = [9, 6, 4, 5, 2, 3, 7, 0, 8, 1];
    let newArray = [];
    while (newArray.length < rawArray.length) newArray.push(rawArray[newDigits[newArray.length]]);
    return newArray.join("");
}

function compute(input) {
    let output = obfuscate(parseInt(input));
    document.getElementById("result").innerText = output;
}

compute(1);
    <input type="number" id="thevalue" value="1" onchange="compute(this.value)">
<div id="result"></div>
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search