Skip to content

Instantly share code, notes, and snippets.

@fcarrero
Forked from beginor/snowflake-id.sql
Created October 10, 2022 17:16
Show Gist options
  • Save fcarrero/5e1713d3291f05c731b7b76a48875c39 to your computer and use it in GitHub Desktop.
Save fcarrero/5e1713d3291f05c731b7b76a48875c39 to your computer and use it in GitHub Desktop.
Twitter Snowflake ID for PostgreSQL
CREATE SEQUENCE public.global_id_seq;
ALTER SEQUENCE public.global_id_seq OWNER TO postgres;
CREATE OR REPLACE FUNCTION public.id_generator()
RETURNS bigint
LANGUAGE 'plpgsql'
AS $BODY$
DECLARE
our_epoch bigint := 1314220021721;
seq_id bigint;
now_millis bigint;
-- the id of this DB shard, must be set for each
-- schema shard you have - you could pass this as a parameter too
shard_id int := 1;
result bigint:= 0;
BEGIN
SELECT nextval('public.global_id_seq') % 1024 INTO seq_id;
SELECT FLOOR(EXTRACT(EPOCH FROM clock_timestamp()) * 1000) INTO now_millis;
result := (now_millis - our_epoch) << 23;
result := result | (shard_id << 10);
result := result | (seq_id);
return result;
END;
$BODY$;
ALTER FUNCTION public.id_generator() OWNER TO postgres;
-- SELECT public.id_generator()
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment