doc: convert between UUID and bytea, Base64, and Base32

[coolaj86]

You can use uuid_send to convert to bytea, but there's no reciprocal built-in.

UUID Cast

SELECT '0192092e-0dc8-76f0-8fa5-d040c4b6d913'::UUID AS uuidv7;

UUID to bytea and back again

WITH "example_uuid_table" AS (
    SELECT '0192092e-0dc8-76f0-8fa5-d040c4b6d913'::UUID AS "ex_uuid_col"
)
SELECT encode(uuid_send("example_uuid_table"."ex_uuid_col"), 'hex')::UUID AS "selected_uuid_col"
FROM "example_uuid_table" \gx

-[ RECORD 1 ]-----+-------------------------------------
selected_uuid_col | 0192092e-0dc8-76f0-8fa5-d040c4b6d913

Note: actually, uuid_recv does exist as a built-in, but it doesn't work as you'd expect (it's not reciprocal), and you can't find documentation for it from the search on the postgres site or most other forms of search... so we make do.

Using bytea_to_uuid

CREATE OR REPLACE FUNCTION "bytea_to_uuid" (input BYTEA)
RETURNS UUID AS $$
BEGIN
    RETURN encode(input, 'hex')::UUID;
END;
$$ LANGUAGE plpgsql;

WITH "example_uuid_table" AS (
    SELECT '0192092e-0dc8-76f0-8fa5-d040c4b6d913'::UUID AS "ex_uuid_col"
)
SELECT uuid_recv(uuid_send("example_uuid_table"."ex_uuid_col")) AS "selected_uuid_col"
FROM "example_uuid_table" \gx

-[ RECORD 1 ]-----+-------------------------------------
selected_uuid_col | 0192092e-0dc8-76f0-8fa5-d040c4b6d913

UUID to base32 and back again

This relies on the crockford base32 functions defined in #6.

WITH "example_uuid_table" AS (
    SELECT '0192092e-0dc8-76f0-8fa5-d040c4b6d913'::UUID AS "ex_uuid_col"
)
SELECT bytea_to_crockford_base32(uuid_send("example_uuid_table"."ex_uuid_col")) AS "base32_encoded_uuid"
FROM "example_uuid_table" \gx

-[ RECORD 1 ]-------+---------------------------
base32_encoded_uuid | 0690jbgds1vf13x5t10c9dps2c

WITH "example_base32_table" AS (
    SELECT '0690jbgds1vf13x5t10c9dps2c' AS "ex_base32_col"
)
SELECT encode(crockford_base32_to_bytea("example_base32_table"."ex_base32_col"), 'hex')::UUID AS "decoded_uuid_from_base32"
FROM "example_base32_table" \gx

-[ RECORD 1 ]------------+-------------------------------------
decoded_uuid_from_base32 | 0192092e-0dc8-76f0-8fa5-d040c4b6d913

UUID to url-safe base64 and back again

This relies on the (very simple) url-safe base64 functions defined in #7.

WITH "example_uuid_table" AS (
    SELECT '0192092e-0dc8-76f0-8fa5-d040c4b6d913'::UUID AS "ex_uuid_col"
)
SELECT bytea_to_urlsafe_base64(uuid_send("example_uuid_table"."ex_uuid_col")) AS "base64_encoded_uuid"
FROM "example_uuid_table" \gx

-[ RECORD 1 ]-------+-----------------------
base64_encoded_uuid | AZIJLg3IdvCPpdBAxLbZEw

WITH "example_base64_table" AS (
    SELECT 'AZIJLg3IdvCPpdBAxLbZEw' AS "ex_base64_col"
)
SELECT encode(urlsafe_base64_to_bytea("example_base64_table"."ex_base64_col"), 'hex')::UUID AS "decoded_uuid_from_base64"
FROM "example_base64_table" \gx

-[ RECORD 1 ]------------+-------------------------------------
decoded_uuid_from_base64 | 0192092e-0dc8-76f0-8fa5-d040c4b6d913

License

The code snippets and documentation in this issue are licensed under the CC0-1.0 (Public Domain), as follows:

PostgreSQL BYTEA to UUID - - convert between UUID and BYTEA, reciprocal to uuid_send

Authored in 2024 by AJ ONeal [email protected]
To the extent possible under law, the author(s) have dedicated all copyright
and related and neighboring rights to this software to the public domain
worldwide. This software is distributed without any warranty.

You should have received a copy of the CC0 Public Domain Dedication along with
this software. If not, see https://creativecommons.org/publicdomain/zero/1.0/