Skip to content

Instantly share code, notes, and snippets.

@beginor

beginor/snowflake-id.sql

Last active September 14, 2025 10:03
Show Gist options
  • Save beginor/9d9f90bc58e1313f6aecd107f8296732 to your computer and use it in GitHub Desktop.
Save beginor/9d9f90bc58e1313f6aecd107f8296732 to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. beginor revised this gist Jun 12, 2018. 1 changed file with 1 addition and 1 deletion.
    2 changes: 1 addition & 1 deletion snowflake-id.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -12,7 +12,7 @@ DECLARE
    -- 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;
    result bigint:= 0;
    BEGIN
    SELECT nextval('public.global_id_seq') % 1024 INTO seq_id;

  2. beginor revised this gist Jun 12, 2018. 1 changed file with 4 additions and 2 deletions.
    6 changes: 4 additions & 2 deletions snowflake-id.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -1,7 +1,7 @@
    CREATE SEQUENCE public.global_id_seq;
    ALTER SEQUENCE public.global_id_seq OWNER TO postgres;

    CREATE FUNCTION public.id_generator()
    CREATE OR REPLACE FUNCTION public.id_generator()
    RETURNS bigint
    LANGUAGE 'plpgsql'
    AS $BODY$
    @@ -12,16 +12,18 @@ DECLARE
    -- 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();
    -- SELECT public.id_generator()
  3. beginor revised this gist Jun 12, 2018. 1 changed file with 11 additions and 5 deletions.
    16 changes: 11 additions & 5 deletions snowflake-id.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -1,6 +1,10 @@
    create sequence public.global_id_sequence;
    CREATE SEQUENCE public.global_id_seq;
    ALTER SEQUENCE public.global_id_seq OWNER TO postgres;

    CREATE OR REPLACE FUNCTION public.id_generator(OUT result bigint) AS $$
    CREATE FUNCTION public.id_generator()
    RETURNS bigint
    LANGUAGE 'plpgsql'
    AS $BODY$
    DECLARE
    our_epoch bigint := 1314220021721;
    seq_id bigint;
    @@ -9,13 +13,15 @@ DECLARE
    -- 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 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);
    END;
    $$ LANGUAGE PLPGSQL;
    $BODY$;

    select public.id_generator();
    ALTER FUNCTION public.id_generator() OWNER TO postgres;

    -- select public.id_generator();
  4. beginor revised this gist Jun 12, 2018. No changes.
  5. beginor created this gist Jun 12, 2018.
    21 changes: 21 additions & 0 deletions snowflake-id.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,21 @@
    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();