Re: Sequence vs UUID - Mailing list pgsql-general

On Wed, Feb 8, 2023 at 11:56 AM Kirk Wolak <wolakk@gmail.com> wrote:
>
> CREATE FUNCTION generate_ulid() RETURNS uuid
>     LANGUAGE sql
> RETURN ((lpad(to_hex((floor((EXTRACT(epoch FROM clock_timestamp()) * (1000000)::numeric)))::bigint), 14, '0'::text)
>       || encode(gen_random_bytes(9), 'hex'::text)))::uuid;

You can save yourself some CPU by skipping the extra cast, omitting
the lpad, to_hex, and floor, and just grabbing the bytes from the
bigint directly along with the random part since bytea can be
concatenated.

    SELECT encode(int8send((EXTRACT(epoch FROM clock_timestamp()) *
1000000)::bigint)
                  || gen_random_bytes(8), 'hex')::uuid
    ;

Note that you refer to it as a ULID, but it is stored as a UUID.
Hopefully nothing downstream ever relies on UUID versioning/spec
compliance. Now that I think of it, I could probably speed up my
tagged interval UUID implementation using some of this at the expense
of configurability.