How to handle short UUIDs with Postgres?

Question

I see that many web services (Stripe comes to mind) use a special encoding for their UUIDs. Instead of the usual encoding a44521d0-0fb8-4ade-8002-3385545c3318 they are going to be encoded using a special alphabet (usually lowercase, uppercase letters and numbers), which results in a UUID like mhvXdrZT4jP5T8vBxuvm75. Both represent the same UUID but with a different encoding.

I'm wondering how these services handle these IDs? Is it possible with Postgres in particular to directly use this short ID encoding in the database? (in other words, any select or insert would use the short ID)

Or would it make sense to save these short IDs directly as text in the database?

I can't find much info about this so I'm not sure what's the best practice is here.

Answer 1

The text you pass to the database from your client (psql or pgAdmin for example) is not the uuid. It is just the text representation of the uuid. Similarly the hexadecimal string you read in your client is also not the uuid - it is that same text representation of the uuid.

...would it make sense to save these short IDs directly as text in the database?

No. Use the uuid datatype for database work with uuids; handle special text formatting as near to the client as you can. On the docs page, you will find the sampling of default permissible input string formats for ad-hoc work. Notable comments though are...

PostgreSQL also accepts the following alternative forms for input: use of upper-case digits, the standard format surrounded by braces, omitting some or all hyphens, adding a hyphen after any group of four digits. Examples are:

BUT

Output is always in the standard form.

If you really, really want your database to do the legwork of base64 encoding your uuid, you can use the functions encode and uuid_send_{(undocumented)} as seen here

 select encode(uuid_send('a44521d0-0fb8-4ade-8002-3385545c3318'::uuid),'base64');

Note that this does not change the uuid itself and you still should not be storing your uuid as text (or bytea, or anything "clever") for that matter. Store your uuid as a uuid. Do the funny string transformations in the app layer.

As for sending a base64 string into the db and then comparing it against stored uuid types, that is a conversion question already answered and explained here

select substring(decode('pEUh0A+4St6AAjOFVFwzGA==','base64')::text from 3)::uuid

Note also that the example uuid (classic form) given in the OP doesn't appear to correspond to the example base64 string given (just using https://www.fileformat.info/tool/guid-base64.htm). Not sure if something more clever is happening there or I'm missing something about the conversion protocol, just fyi ̄\_(ツ)_/ ̄

Answer 2

UUIDs are in fact 16 byte long binary strings and you normally want to store and manipulate them as such. Only when passing UUIDs via a protocol that is not capable of dealing with binary strings (e.g. HTTP queries) you need to serialize them as character strings. The two common methods you are referring to in your question are hexadecimal representation (a44521d0-0fb8-4ade-8002-3385545c3318) and base-64 encoding (mhvXdrZT4jP5T8vBxuvm75). Pick the one that you like and that is compatible with upstream and downstream applications.

• laurent: Thanks, but what I mean is that when I query the database, it's going to be using the hexadecimal representation, so I'm wondering if Postgres can instead use base-64 encoding? If not, is there any good practice to handle this? It seems to add a lot of complexity to the app to have to convert back and forth between short and long IDs.
• RonJohn: @laurent "It seems to add a lot of complexity to the app to have to convert back and forth between short and long IDs." That's the price you pay for interfacing with products which try to be clever.