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

INSERT INTO the_table(uuid_column) VALUES ($1)

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

INSERT INTO the_table(uuid_column) VALUES (CAST(ENCODE($1, 'hex') AS UUID))

and that worked great!