My employer would like me to generate a unique ID for all users in a table based on their first name, last name, and birthdate. They format is to be like this:

First 3 letters of the last name, first 2 letters of the first name, last two digits of the birth year so for a user John Doe, born in 1990 the ID will be DOEJO90.

The problem is we sometimes have collisions with this ID so they want me to append an extra digit to the end, so if we have a second DOEJO90 it will become DOEJO901. Is there a way to do this natively in postgres?

I’m able to create the ID but I’m unsure how to determine if there is a collision for that ID.