How to handle short UUIDs with Postgres?

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.

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 ¯\_(ツ)_/¯

StackExchange Database Administrators Q#307520, answer score: 9