Skip to content

Instantly share code, notes, and snippets.

@Sytten
Created June 6, 2022 03:00
Show Gist options
  • Save Sytten/8fa334e8a5de5d49924c794811a809fb to your computer and use it in GitHub Desktop.
Save Sytten/8fa334e8a5de5d49924c794811a809fb to your computer and use it in GitHub Desktop.
ULID PL/pgSQL
CREATE EXTENSION IF NOT EXISTS pgcrypto;
CREATE OR REPLACE FUNCTION uuid_generate_ulid ()
RETURNS uuid
AS $$
DECLARE
timestamp bytea = E'\\000\\000\\000\\000\\000\\000';
unix_time bigint;
BEGIN
unix_time = (EXTRACT(EPOCH FROM clock_timestamp()) * 1000)::bigint;
timestamp = SET_BYTE(timestamp, 0, (unix_time >> 40)::bit(8)::integer);
timestamp = SET_BYTE(timestamp, 1, (unix_time >> 32)::bit(8)::integer);
timestamp = SET_BYTE(timestamp, 2, (unix_time >> 24)::bit(8)::integer);
timestamp = SET_BYTE(timestamp, 3, (unix_time >> 16)::bit(8)::integer);
timestamp = SET_BYTE(timestamp, 4, (unix_time >> 8)::bit(8)::integer);
timestamp = SET_BYTE(timestamp, 5, unix_time::bit(8)::integer);
RETURN encode(timestamp || gen_random_bytes(10), 'hex')::uuid;
END
$$
LANGUAGE plpgsql
VOLATILE;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment