Converting byte arrays to UUIDs in Postgres
For a project that I’m working on, I have a custom flake id spec
that allows me to generate unique, sortable identifiers across
computers without any sort of synchronization. The ids themselves can
be encoded down to 16 bytes and I wanted to store them in Postgres. A
good way to do that is to leverage Postgres’ UUID
data type, which
lets you efficiently store any 16 byte quantity in a way that can be
indexed reasonably well.
The problem I ran into was that my DB library of choice only supports inserting UUID values that follow the standard UUID format, so queries like
|
|
would get rejected at runtime unless $1
actually looked like a UUID.
I considered converting the ids into the standard UUID format within
my application code but that didn’t feel like the right thing to do.
Instead, I found that Postgres has a standard function called
encode
that is able to take any byte array and encode it into a
hex string so all I had to do was change my query into
|
|
and that worked great!