geckoboard / pgulid Goto Github PK

I wrote the following, heavily inspired by what you already wrote:

CREATE FUNCTION public.generate_ulid() RETURNS uuid
    LANGUAGE plpgsql
    AS $$
DECLARE
  timestamp  BYTEA = E'\\000\\000\\000\\000\\000\\000';
  unix_time  BIGINT;
BEGIN
    unix_time = (EXTRACT(EPOCH FROM NOW()) * 1000)::BIGINT;
    timestamp = SET_BYTE(timestamp, 3, (unix_time >> 40)::BIT(8)::INTEGER);
    timestamp = SET_BYTE(timestamp, 2, (unix_time >> 32)::BIT(8)::INTEGER);
    timestamp = SET_BYTE(timestamp, 1, (unix_time >> 24)::BIT(8)::INTEGER);
    timestamp = SET_BYTE(timestamp, 0, (unix_time >> 16)::BIT(8)::INTEGER);
    timestamp = SET_BYTE(timestamp, 5, (unix_time >> 8)::BIT(8)::INTEGER);
    timestamp = SET_BYTE(timestamp, 4, unix_time::BIT(8)::INTEGER);
    RETURN CAST(substring(CAST((timestamp || gen_random_bytes(10)) AS text) from 3) AS uuid);
END
$$;

It generates a ULID, but in the UUID format. The order of the bytes is a bit unintuitive due to the UUID format/endianness.

This function makes it easier to use the uuid PostGres type to hold ULIDs. This makes for smaller, faster indexes (c.f. indexing on text).

Howdy thanks for creating this really useful function! To use this more easily, I ended up adding a new domain type for the text result of this function so it can be used more like the uuid type that its replacing. The one extra SQL statement I added:

create domain ulid as text 
default generate_ulid() 
not null 
constraint ulid_length_check check(char_length(value) = 26)
constraint ulid_upper_bound check(value<='7ZZZZZZZZZZZZZZZZZZZZZZZZZ');

this add a few standard checks for string length and sets the max value (per the ULID spec) to prevent invalid IDs from sneaking in. It also allows you to use the new ulid type in table creation statements in a clean way:

CREATE TABLE example_table
(
  id                        ulid primary key,
  ...other columns
);

Was gonna open a PR but since it is such a small change thought this might be easier to add to the discussion here.

Here is the link in the spec: https://github.com/ulid/spec#monotonicity

Without monotonic ULIDs one cannot truly sort by this key in a high-append rate situation.

Hello 👋🏼, would it be possible to add a LICENSE file to this repository (maybe using the same as is in the original js implementation)?

The code is extremely useful, but for some vendors it cannot be installed as a plugin (i.e., AWS Postgres), meaning we'd need to fork the code into a custom function, but not having a license makes that problematic.