Skip to content

Instantly share code, notes, and snippets.

@shayded-exe
Last active October 9, 2021 14:32
Show Gist options
  • Save shayded-exe/f4c2e26829d82ed8d38eb5e6e6374ec2 to your computer and use it in GitHub Desktop.
Save shayded-exe/f4c2e26829d82ed8d38eb5e6e6374ec2 to your computer and use it in GitHub Desktop.
pgSQL time-based sequential UUID generator based on https://www.2ndquadrant.com/en/blog/sequential-uuid-generators/
CREATE OR REPLACE FUNCTION generate_sequential_uuid(p_interval_length int DEFAULT 60)
RETURNS uuid
LANGUAGE plpgsql
AS $$
DECLARE
v_i int;
v_time bigint;
v_bytes int[16] = '{}';
v_hex text[16] = '{}';
BEGIN
v_time := floor(extract(epoch FROM clock_timestamp()) / p_interval_length);
v_bytes[1] := v_time >> 8 & 255;
v_bytes[2] := v_time & 255;
FOR v_i IN 3..16 LOOP
v_bytes[v_i] := floor(random() * 256);
END LOOP;
FOR v_i IN 1..16 LOOP
v_hex[v_i] := lpad(to_hex(v_bytes[v_i]), 2, '0');
END LOOP;
RETURN array_to_string(v_hex, '');
END $$;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment