Snowflake'ish Id generation with PostgreSQL

snowflake-ish-postgres.sql

-- Modified from https://instagram-engineering.com/sharding-ids-at-instagram-1cf5a71e5a5c
-- And https://gist.github.com/yohang88/f950fbb239192866895a
-- And https://rextester.com/NPNU50638

CREATE OR REPLACE FUNCTION message.next_id() RETURNS TRIGGER AS $$
DECLARE
    our_epoch bigint := 1314220021721;
    seq_id bigint;
    now_millis bigint;
    -- the id of this DB shard is either hard coded, or
    -- here, `message_id` is used instead
    shard_id int;
    result bigint;
BEGIN
    IF NEW.message_id IS NULL THEN
      -- hard code to 1, or whatever
      SELECT 1 INTO shard_id;
    ELSE
      SELECT NEW.message_id % 8192 INTO shard_id;
    END IF;
    SELECT nextval('message.table_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);
    NEW.id = result;
    return NEW;
END;
    $$ LANGUAGE PLPGSQL;


CREATE TABLE messages (
  id BIGINT NOT NULL PRIMARY KEY,
  message_id BIGINT NOT NULL
);


CREATE TRIGGER trigger_insert_messages_id
BEFORE INSERT
ON messages
FOR EACH ROW
EXECUTE PROCEDURE message.next_id();


INSERT INTO messages(message_id) values(1);
INSERT INTO messages(message_id) values(2);
INSERT INTO messages(message_id) values(2);


SELECT * FROM messages;
/*
         id          | message_id 
---------------------+------------
 3074421248655098886 |          1
 3074421458806507527 |          2
 3074421558270232584 |          2
(3 rows)
*/