geckoboard / pgulid Goto Github PK
View Code? Open in Web Editor NEWUniversally Unique Lexicographically Sortable Identifier (ULID) for PostgreSQL
Universally Unique Lexicographically Sortable Identifier (ULID) for PostgreSQL
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.
A declarative, efficient, and flexible JavaScript library for building user interfaces.
๐ Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
An Open Source Machine Learning Framework for Everyone
The Web framework for perfectionists with deadlines.
A PHP framework for web artisans
Bring data to life with SVG, Canvas and HTML. ๐๐๐
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
Some thing interesting about web. New door for the world.
A server is a program made to process requests and deliver data to clients.
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
Some thing interesting about visualization, use data art
Some thing interesting about game, make everyone happy.
We are working to build community through open source technology. NB: members must have two-factor auth.
Open source projects and samples from Microsoft.
Google โค๏ธ Open Source for everyone.
Alibaba Open Source for everyone
Data-Driven Documents codes.
China tencent open source team.