Generating UUIDv7 in PostgreSQL
###############################

:category:  Misc
:date:      2024-01-20 07:50:00 +0200
:tags:      sql, uuid, uuidv7, postgres

But what about Postgres?
We can do it too.
Hopefully generators for new UUID versions will be added to uuid-ossp but it is not yet the case.

The easiest way for now is to use pgcrypto, so let's start with that:

.. code-block:: sql

    create extension pgcrypto;

Now port the solution from `the MySQL post`__:

.. __: link://slug/mysql-uuid-v7

.. code-block:: sql

    select
        lpad(to_hex((extract(epoch from now()) * 1000)::bigint), '12', '0') ||
        '7' || -- version
        substr(encode(gen_random_bytes(2), 'hex'), 2) ||
        to_hex((floor(random() * 4) + 8)::int) || -- variant bits
        substr(encode(gen_random_bytes(8), 'hex'), 2)
        as uuid;

Like with MariaDB, Postgres doesn't care about dashes on insertion,
so this value can be directly applied to a field with the uuid type.

And again, to make it a formatted value, use a type cast:

.. code-block:: sql

    select
        (lpad(to_hex((extract(epoch from now()) * 1000)::bigint), '12', '0') ||
        '7' ||
        substr(encode(gen_random_bytes(2), 'hex'), 2) ||
        to_hex((floor(random() * 4) + 8)::int) ||
        substr(encode(gen_random_bytes(8), 'hex'), 2))::uuid
        as uuid;

And to have a bytea value, use decode:

.. code-block:: sql

    select
        decode(
            lpad(to_hex((extract(epoch from now()) * 1000)::bigint), '12', '0') ||
            '7' ||
            substr(encode(gen_random_bytes(2), 'hex'), 2) ||
            to_hex((floor(random() * 4) + 8)::int) ||
            substr(encode(gen_random_bytes(8), 'hex'), 2),
            'hex'
        ) as uuid;