# Snowflake ID Implementation for PostgreSQL

[TOC]

---

## 建立 sequence

CREATE SEQUENCE IF NOT EXISTS "public"."global_id_sequence";

## 建立 function

CREATE OR REPLACE FUNCTION "public"."id_generator"(OUT result bigint) AS $$
DECLARE
	our_epoch bigint := 857736000000; -- change as needed
	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('public.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"();

## 使用於 DDL

CREATE TABLE "public"."some_table" (
	"id" bigint DEFAULT "public"."id_generator"() PRIMARY KEY,
	-- omitted for brevity
);

## 參考資料來源

- [A Better ID Generator For PostgreSQL](https://rob.conery.io/2014/05/29/a-better-id-generator-for-postgresql/)