Skip to content

Instantly share code, notes, and snippets.

@devdlabs

devdlabs/snowflake-id.sql

Forked from beginor/snowflake-id.sql
Created October 20, 2020 13:36
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save devdlabs/dfd67db71274aa187a85ff7d4d6a7247 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save devdlabs/dfd67db71274aa187a85ff7d4d6a7247 to your computer and use it in GitHub Desktop.
Download ZIP
Twitter Snowflake ID for PostgreSQL
Raw
snowflake-id.sql
create sequence public.global_id_sequence;
CREATE OR REPLACE FUNCTION public.id_generator(OUT result bigint) AS $$
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;
BEGIN
SELECT nextval('shard_1.global_id_sequence') % 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);
END;
$$ LANGUAGE PLPGSQL;
select public.id_generator();
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment