How to convert a PostgreSQL UUID to bytea

uuid_to_bytea.sql

select decode(replace('45774962-e6f7-41f6-b940-72ef63fa1943'::text, '-', ''), 'hex');

-- And here's how to convert it to the ShortUUID format used in Process Street
select replace(encode(substring(decode(replace('45774962-e6f7-41f6-b940-72ef63fa1943'::text, '-', ''), 'hex') from 9 for 8) || 
substring(decode(replace('45774962-e6f7-41f6-b940-72ef63fa1943'::text, '-', ''), 'hex') from 1 for 8), 'base64'), '=', '');

will=# select uuid_send('45774962-e6f7-41f6-b940-72ef63fa1943'::uuid);
             uuid_send
------------------------------------
 \x45774962e6f741f6b94072ef63fa1943

Here's a function for it:

create function uuid_to_muid(id uuid)
returns text
as $$
  select translate(
    encode(
      substring(decode(replace(id::text, '-', ''), 'hex') from 9 for 8) || 
      substring(decode(replace(id::text, '-', ''), 'hex') from 1 for 8), 
      'base64'
    ), 
    '+/=', '-_'
  );
$$
LANGUAGE SQL
IMMUTABLE
RETURNS NULL ON NULL INPUT;

Here's a function for the reverse:

create function muid_to_uuid(id text)
returns uuid
as $$
  select 
    (encode(substring(bin from 9 for 9), 'hex') || encode(substring(bin from 0 for 9), 'hex'))::uuid
  from decode(translate(id, '-_', '+/') || '==', 'base64') as bin;
$$
LANGUAGE SQL
IMMUTABLE
RETURNS NULL ON NULL INPUT;

What I need is true base62 conversion for the whole 128-bit UUID :-/

@will thank you!

@Mart-Bogdan

@will thank you!

:)

Also in case you didn’t know (and anyone in the future) there is a _send function for all data types, if you want to get the internal binary representation of the data. Just something to keep in the back of your mind, I find it's handy to know like once every few years

@will any idea what the use case for void_send would be? how is it even called?