PostgreSQL int to UUID

Brian Pontarelli
  • By Brian Pontarelli
  • Technology
  • September 23, 2015

I’ve been working to convert a bunch of our database columns from integers to UUIDs. I was having a hard time figuring out how to handle this conversion easily in PostgreSQL. For some reason, the PostgreSQL developers decided that the int data type was not converted to UUID automatically. This is somewhat shocking because both of these data types are binary types with different lengths (int being 4 and UUID being 16),. Since I couldn’t submit a feature request and wait 2-3 years to have it implemented, I had to find a solution that worked in an SQL script easily.

After some playing around and hacking in sql, I figured out the solution. Here’s a little snippet of my solution:

The trick here is you need to convert the integer column to a hexadecimal string and then pad it with zeros. Since PostgreSQL happy converts strings to UUIDs, you can just cast the string to a UUID. Simple!

  • Trashcan4U

    The reason is because if you convert INT into UUID for more than 1 table, the resulting ID values will not be “unique” anymore.

    But more importantly, what do you do with relations between tables? You will loose all the connections. Your example would work if you have only 1 table in your whole database 🙂

    • Brian Pontarelli

      While a true UUID is universally unique, in practice, UUIDs are just integers and don’t need to be unique at all. Therefore, it you use the contract of the database table you are converting, then using UUIDs as a data type should work fine.

      For example, a single table in a database might use an auto incremented column of type integer for its primary key. Another table might do the same thing. These two tables might both have a row with a value of 1. This doesn’t mean that you can’t convert these columns to the UUID data type.

      Additionally, UUIDs aren’t universally identifiable. By that I mean, if I give you a UUID, you’ll have no idea what the context of it is. Therefore, I could technically hand you a UUID that is being used in some database in Florida, but you will never collide with that value because you’ll never have access to that database.

      In practice, it often makes sense to convert columns from integers to UUIDs so that you can start using type-4 UUIDs going forward while the existing rows will still work because they won’t collide with anything else in that specific table.

      • Trashcan4U

        The problem is, while technically you can put whatever to UUID columns. In real life, they should to be unique. Otherwise people will have problems sooner or later. Many applications of the UUIDs rely on the fact that they are unique.

        For example. If you were working in a company which has an office in Florida and California and they want to merge their user databases. At some point they convert their user IDs to UUID using your method, then they try to merge the data… they will have problems due to conflicting rows.

        This is why PostgreSQL should never support a feature like this. Because it will cause problems sooner or later. People will say “I use UUID with PostgreSQL but they are not unique., WTF is going on!@$#” 🙂 If you want to use “non unique”-UUIDs then you have to understand the risks and do it yourself.

        • Brian Pontarelli

          Very true. If you are going to use UUIDs as unique identifiers outside of the database, then they should be universally unique.

          Your example is a good because is assumes that somewhere out in the universe other systems are referencing UUIDs from both the California and the Florida databases. In this case, the two databases cannot use the same UUIDs.

          If all external systems are instead using a unique key like email address to reference users, then it is fine to just throw out the primary key when you merge these two databases and use type-4 UUIDs as a replacement.

          In my specific case, I needed a way to take existing customer data that was using integers for the user ids and convert it to a UUID for Passport to use. The kicker was that they were referencing the integer values from other systems and I therefore had to maintain the value.