Generating UUIDv7 in PostgreSQL

Jan 20, 2024 • Comments •

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:

create extension pgcrypto;

Now port the solution from the MySQL post:

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:

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:

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;