Generate unique usernames with PostgreSQL

I had a task today at work to auto generate usernames based on the first name and the last name. It all comes down to creating a unique username for a users table.

My approach is to create a username that doesn’t exist in the SQL table. The best way I found to create an incremental counter and appending it onto every username so that it becomes unique. That is of course if you allow numbers in your username. If you don’t there are certainly another solution but might be less efficient and harder to implement.

The first thing that came to mind was to concatenate the first name and the last name and then checking if any row existed with that name. If it existed, append a number to the concatenated string and check if that is available as well.

The problem with the approach above is that you have to make N amount of queries to the database to check the availability for your username.

A better approach is to create a query that sorts of returns a unique username based on the data that’s already in the database.

My seconds approach is almost the same as the first one except I don’t retry to check if the username is available. Instead I am fetching the largest appended number to a certain username, incrementing it by one and returning it. I then take that number and appends it to the username. This makes sure that the username is unique. Well, due to the ms delay between performing this query and performing the query that inserts the new username, things can happen in between and there is a slight chance someone snaps the username. In that case, add a unique constraint for your username column, catch the constraint error and do whatever it takes to insert a unique username(most often it is to repeat the same procedure as above).

You can choose the place the counter anywhere in the string, in the middle or in the beginning or split the counter so that one part is in the beginning and the other is at the end. Or maybe a decremented counter.

This is the first post of many to come since I promises myself that I would start a weekly development blog about programming.

One clap, two clap, three clap, forty?

By clapping more or less, you can signal to us which stories really stand out.